Re: Improvements in prepared statements

Hello, some improvements in the prepared statements would facilitate
their use from applications:

- Use of table and column names in prepared statements.

Example: select # from # where # = ?;

- Use of arrays in prepared statements.

Example: select # from article where id in (?);

# = author,title
? = 10,24,45

Best regards.
Alejandro Sánchez.

Hi

po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
napsal:

> Hello, some improvements in the prepared statements would facilitate
> their use from applications:
>
> - Use of table and column names in prepared statements.
>
> Example: select # from # where # = ?;
>
> - Use of arrays in prepared statements.
>
> Example: select # from article where id in (?);
>
> # = author,title
> ? = 10,24,45
>

The server side prepared statements are based on reusing execution plans.
You cannot reuse execution plans if you change table, or column. This is
the reason why SQL identifiers are immutable in prepared statements. There
are client side prepared statements - JDBC does it. There it is possible.
But it is impossible on the server side. Prepared statements are like a
compiled program. You can change parameters, variables - but you cannot
change the program.

Regards

Pavel

>
> Best regards.
> Alejandro Sánchez.
>
>
>
>

Hello, as far as I know it is not done in JDBC, in many frameworks it
is.Although the execution plans cannot be reused it would be
somethingvery useful. It is included in a lot of frameworks and is a
recurrentquestion in database forums. It would be nice if it was
included in plain SQL.
Best regards.Alejandro Sánchez.
El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
> Hi
>
> po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <
> alex(at)nexttypes(dot)com> napsal:
> > Hello, some improvements in the prepared statements would
> > facilitate
> >
> > their use from applications:
> >
> >
> >
> > - Use of table and column names in prepared statements.
> >
> >
> >
> > Example: select # from # where # = ?;
> >
> >
> >
> > - Use of arrays in prepared statements.
> >
> >
> >
> > Example: select # from article where id in (?);
> >
> >
> >
> > # = author,title
> >
> > ? = 10,24,45
>
> The server side prepared statements are based on reusing execution
> plans. You cannot reuse execution plans if you change table, or
> column. This is the reason why SQL identifiers are immutable in
> prepared statements. There are client side prepared statements - JDBC
> does it. There it is possible. But it is impossible on the server
> side. Prepared statements are like a compiled program. You can change
> parameters, variables - but you cannot change the program.
>
> Regards
> Pavel
>
>
>
> >
> > Best regards.
> >
> > Alejandro Sánchez.
> >
> >
> >
> >
> >
> >
> >

po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
napsal:

> Hello, as far as I know it is not done in JDBC, in many frameworks it is.
>
> Although the execution plans cannot be reused it would be something
>
> very useful. It is included in a lot of frameworks and is a recurrent <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>
> question in database forums <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>. It would be nice if it was included in plain
>
> SQL.
>
>
I am very sceptical about it. What benefit do you expect? When you cannot
reuse an execution plan, then there is not any benefit of this. Then you
don't need prepared statements, and all this API is useless. So some
questions are frequent and don't mean necessity to redesign. The developers
just miss the fundamental knowledge of database technology.

Regards

Pavel

> Best regards.
>
> Alejandro Sánchez.
>
>
> El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
>
> Hi
>
> po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> Hello, some improvements in the prepared statements would facilitate
> their use from applications:
>
> - Use of table and column names in prepared statements.
>
> Example: select # from # where # = ?;
>
> - Use of arrays in prepared statements.
>
> Example: select # from article where id in (?);
>
> # = author,title
> ? = 10,24,45
>
>
> The server side prepared statements are based on reusing execution plans.
> You cannot reuse execution plans if you change table, or column. This is
> the reason why SQL identifiers are immutable in prepared statements. There
> are client side prepared statements - JDBC does it. There it is possible.
> But it is impossible on the server side. Prepared statements are like a
> compiled program. You can change parameters, variables - but you cannot
> change the program.
>
> Regards
>
> Pavel
>
>
>
>
>
> Best regards.
> Alejandro Sánchez.
>
>
>
>

The benefit is ease of use. One of the great advantages of prepared
statements is nothaving to concatenate strings. The use of arrays would
also be very useful.
query("select " + column1 + "," + column2 from " " + table + " where id
in (?), ids);
VS
query("select # from # where id in (?)", columns, table, ids);
And it doesn't have to be done with prepared statements, it can just be
another SQL syntax.
El lun, 01-03-2021 a las 16:46 +0100, Pavel Stehule escribió:
> po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <
> alex(at)nexttypes(dot)com> napsal:
> > Hello, as far as I know it is not done in JDBC, in many frameworks
> > it is.Although the execution plans cannot be reused it would be
> > somethingvery useful. It is included in a lot of frameworks and is
> > a recurrentquestion in database forums. It would be nice if it was
> > included in plain SQL.
>
> I am very sceptical about it. What benefit do you expect? When you
> cannot reuse an execution plan, then there is not any benefit of
> this. Then you don't need prepared statements, and all this API is
> useless. So some questions are frequent and don't mean necessity to
> redesign. The developers just miss the fundamental knowledge of
> database technology.
>
> Regards
> Pavel
>
> > Best regards.Alejandro Sánchez.
> > El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
> > > Hi
> > >
> > > po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <
> > > alex(at)nexttypes(dot)com> napsal:
> > > > Hello, some improvements in the prepared statements would
> > > > facilitate
> > > >
> > > > their use from applications:
> > > >
> > > >
> > > >
> > > > - Use of table and column names in prepared statements.
> > > >
> > > >
> > > >
> > > > Example: select # from # where # = ?;
> > > >
> > > >
> > > >
> > > > - Use of arrays in prepared statements.
> > > >
> > > >
> > > >
> > > > Example: select # from article where id in (?);
> > > >
> > > >
> > > >
> > > > # = author,title
> > > >
> > > > ? = 10,24,45
> > >
> > > The server side prepared statements are based on reusing
> > > execution plans. You cannot reuse execution plans if you change
> > > table, or column. This is the reason why SQL identifiers are
> > > immutable in prepared statements. There are client side prepared
> > > statements - JDBC does it. There it is possible. But it is
> > > impossible on the server side. Prepared statements are like a
> > > compiled program. You can change parameters, variables - but you
> > > cannot change the program.
> > >
> > > Regards
> > > Pavel
> > >
> > >
> > >
> > > >
> > > > Best regards.
> > > >
> > > > Alejandro Sánchez.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >

po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
napsal:

> The benefit is ease of use. One of the great advantages of prepared statements is not
>
> having to concatenate strings. The use of arrays would also be very useful.
>
>
> query("select " + column1 + "," + column2 from " " + table + " where id in (?), ids);
>
>
> VS
>
>
> query("select # from # where id in (?)", columns, table, ids);
>
>
> And it doesn't have to be done with prepared statements, it can just be another SQL syntax.
>
>
This is not too strong an argument - any language (and Database API) has
necessary functionality now. Just you should use it.

You can use fprintf in php, format in plpgsql, String.Format in C#, Java,
...

Regards

Pavel

> El lun, 01-03-2021 a las 16:46 +0100, Pavel Stehule escribió:
>
>
>
> po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> Hello, as far as I know it is not done in JDBC, in many frameworks it is.
>
> Although the execution plans cannot be reused it would be something
>
> very useful.
>
> It is included in a lot of frameworks and is
>
> a recurrent
>
>
> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>
> question in database forums
>
>
> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>
> . I
>
> t
>
> would be nice if it was included in plain
>
> SQL.
>
>
>
> I am very sceptical about it. What benefit do you expect? When you cannot
> reuse an execution plan, then there is not any benefit of this. Then you
> don't need prepared statements, and all this API is useless. So some
> questions are frequent and don't mean necessity to redesign. The developers
> just miss the fundamental knowledge of database technology.
>
> Regards
>
> Pavel
>
>
> Best regards.
>
> Alejandro Sánchez.
>
>
> El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
>
> Hi
>
> po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> Hello, some improvements in the prepared statements would facilitate
> their use from applications:
>
> - Use of table and column names in prepared statements.
>
> Example: select # from # where # = ?;
>
> - Use of arrays in prepared statements.
>
> Example: select # from article where id in (?);
>
> # = author,title
> ? = 10,24,45
>
>
> The server side prepared statements are based on reusing execution plans.
> You cannot reuse execution plans if you change table, or column. This is
> the reason why SQL identifiers are immutable in prepared statements. There
> are client side prepared statements - JDBC does it. There it is possible.
> But it is impossible on the server side. Prepared statements are like a
> compiled program. You can change parameters, variables - but you cannot
> change the program.
>
> Regards
>
> Pavel
>
>
>
>
>
> Best regards.
> Alejandro Sánchez.
>
>
>
>

po 1. 3. 2021 v 17:15 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
>> The benefit is ease of use. One of the great advantages of prepared statements is not
>>
>> having to concatenate strings. The use of arrays would also be very useful.
>>
>>
>> query("select " + column1 + "," + column2 from " " + table + " where id in (?), ids);
>>
>>
>>
The argument with arrays is not good. You can work with arrays just on
binary level, that is more effective. But just you should use operator =
ANY() instead IN.

Regards

Pavel

> VS
>>
>>
>> query("select # from # where id in (?)", columns, table, ids);
>>
>>
>> And it doesn't have to be done with prepared statements, it can just be another SQL syntax.
>>
>>
> This is not too strong an argument - any language (and Database API) has
> necessary functionality now. Just you should use it.
>
> You can use fprintf in php, format in plpgsql, String.Format in C#, Java,
> ...
>
> Regards
>
> Pavel
>
>
>
>
>
>> El lun, 01-03-2021 a las 16:46 +0100, Pavel Stehule escribió:
>>
>>
>>
>> po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
>> napsal:
>>
>> Hello, as far as I know it is not done in JDBC, in many frameworks it is.
>>
>> Although the execution plans cannot be reused it would be something
>>
>> very useful.
>>
>> It is included in a lot of frameworks and is
>>
>> a recurrent
>>
>>
>> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>>
>> question in database forums
>>
>>
>> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>>
>> . I
>>
>> t
>>
>> would be nice if it was included in plain
>>
>> SQL.
>>
>>
>>
>> I am very sceptical about it. What benefit do you expect? When you cannot
>> reuse an execution plan, then there is not any benefit of this. Then you
>> don't need prepared statements, and all this API is useless. So some
>> questions are frequent and don't mean necessity to redesign. The developers
>> just miss the fundamental knowledge of database technology.
>>
>> Regards
>>
>> Pavel
>>
>>
>> Best regards.
>>
>> Alejandro Sánchez.
>>
>>
>> El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
>>
>> Hi
>>
>> po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
>> napsal:
>>
>> Hello, some improvements in the prepared statements would facilitate
>> their use from applications:
>>
>> - Use of table and column names in prepared statements.
>>
>> Example: select # from # where # = ?;
>>
>> - Use of arrays in prepared statements.
>>
>> Example: select # from article where id in (?);
>>
>> # = author,title
>> ? = 10,24,45
>>
>>
>> The server side prepared statements are based on reusing execution plans.
>> You cannot reuse execution plans if you change table, or column. This is
>> the reason why SQL identifiers are immutable in prepared statements. There
>> are client side prepared statements - JDBC does it. There it is possible.
>> But it is impossible on the server side. Prepared statements are like a
>> compiled program. You can change parameters, variables - but you cannot
>> change the program.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>> Best regards.
>> Alejandro Sánchez.
>>
>>
>>
>>

It is a matter of taste. I think this functionality would be better in
SQLand be the same for all languages without the need to use string
functions.
El lun, 01-03-2021 a las 17:15 +0100, Pavel Stehule escribió:
> po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <
> alex(at)nexttypes(dot)com> napsal:
> > The benefit is ease of use. One of the great advantages of prepared
> > statements is nothaving to concatenate strings. The use of arrays
> > would also be very useful.
> > query("select " + column1 + "," + column2 from " " + table + "
> > where id in (?), ids);
> > VS
> > query("select # from # where id in (?)", columns, table, ids);
> >
> > And it doesn't have to be done with prepared statements, it can
> > just be another SQL syntax.
>
> This is not too strong an argument - any language (and Database API)
> has necessary functionality now. Just you should use it.
>
> You can use fprintf in php, format in plpgsql, String.Format in C#,
> Java, ...
>
> Regards
>
> Pavel
>
>
>
>
> > El lun, 01-03-2021 a las 16:46 +0100, Pavel Stehule escribió:
> > > po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <
> > > alex(at)nexttypes(dot)com> napsal:
> > > > Hello, as far as I know it is not done in JDBC, in many
> > > > frameworks it is.Although the execution plans cannot be reused
> > > > it would be somethingvery useful. It is included in a lot of
> > > > frameworks and is a recurrentquestion in database forums. It
> > > > would be nice if it was included in plain SQL.
> > >
> > > I am very sceptical about it. What benefit do you expect? When
> > > you cannot reuse an execution plan, then there is not any benefit
> > > of this. Then you don't need prepared statements, and all this
> > > API is useless. So some questions are frequent and don't mean
> > > necessity to redesign. The developers just miss the fundamental
> > > knowledge of database technology.
> > >
> > > Regards
> > > Pavel
> > >
> > > > Best regards.Alejandro Sánchez.
> > > > El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
> > > > > Hi
> > > > >
> > > > > po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <
> > > > > alex(at)nexttypes(dot)com> napsal:
> > > > > > Hello, some improvements in the prepared statements would
> > > > > > facilitate
> > > > > >
> > > > > > their use from applications:
> > > > > >
> > > > > >
> > > > > >
> > > > > > - Use of table and column names in prepared statements.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Example: select # from # where # = ?;
> > > > > >
> > > > > >
> > > > > >
> > > > > > - Use of arrays in prepared statements.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Example: select # from article where id in (?);
> > > > > >
> > > > > >
> > > > > >
> > > > > > # = author,title
> > > > > >
> > > > > > ? = 10,24,45
> > > > >
> > > > > The server side prepared statements are based on reusing
> > > > > execution plans. You cannot reuse execution plans if you
> > > > > change table, or column. This is the reason why SQL
> > > > > identifiers are immutable in prepared statements. There are
> > > > > client side prepared statements - JDBC does it. There it is
> > > > > possible. But it is impossible on the server side. Prepared
> > > > > statements are like a compiled program. You can change
> > > > > parameters, variables - but you cannot change the program.
> > > > >
> > > > > Regards
> > > > > Pavel
> > > > >
> > > > >
> > > > >
> > > > > >
> > > > > > Best regards.
> > > > > >
> > > > > > Alejandro Sánchez.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >

Hi

po 1. 3. 2021 v 17:26 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
napsal:

> It is a matter of taste. I think this functionality would be better in SQL
>
> and be the same for all languages without the need to use string functions.
>
>
You can try to implement it, and send a patch. But I think a) it will be
just string concatenation, and then it is surely useless, or b) you should
introduce a new parser, because current parser need to know SQL identifiers
immediately. But anyway - anybody can write your opinion here. From me - I
don't like this idea.

Regards

Pavel

>
> El lun, 01-03-2021 a las 17:15 +0100, Pavel Stehule escribió:
>
>
>
> po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> The benefit is ease of use. O
>
> ne of the great advantages of prepared statements is not
>
> having to concatenate strings. The use of arrays would also be very useful.
>
>
> query("select " + column1 + "," + column2 from " " + table + " where id in (?), ids);
>
>
> VS
>
>
> query("select # from # where id in (?)", columns, table, ids);
>
>
> And it doesn't have to be done with prepared statements, it can just be another SQL syntax.
>
>
> This is not too strong an argument - any language (and Database API) has
> necessary functionality now. Just you should use it.
>
> You can use fprintf in php, format in plpgsql, String.Format in C#, Java,
> ...
>
> Regards
>
> Pavel
>
>
>
>
>
> El lun, 01-03-2021 a las 16:46 +0100, Pavel Stehule escribió:
>
>
>
> po 1. 3. 2021 v 16:39 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> Hello, as far as I know it is not done in JDBC, in many frameworks it is.
>
> Although the execution plans cannot be reused it would be something
>
> very useful.
>
> It is included in a lot of frameworks and is
>
> a recurrent
>
>
> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>
> question in database forums
>
>
> <https://www.google.com/search?client=firefox-b-e&biw=1016&bih=475&sxsrf=ALeKk03ixEtdOsWcDWjkGcmo_MaTxdKWqw%3A1614613001966&ei=CQo9YKmzOtHlgwfCxoyoCQ&q=prepared+statement+table+name&oq=prepared+statement+table+name&gs_lcp=Cgdnd3Mtd2l6EAMyCwgAELADEMsBEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQBxAeEIsDMgwIABCwAxAHEB4QiwMyDAgAELADEAcQHhCLAzIMCAAQsAMQCBAeEIsDUABYAGDUyQRoAXAAeACAAegMiAHoDJIBAzgtMZgBAKoBB2d3cy13aXrIAQq4AQHAAQE&sclient=gws-wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>
>
> . I
>
> t
>
> would be nice if it was included in plain
>
> SQL.
>
>
>
> I am very sceptical about it. What benefit do you expect? When you cannot
> reuse an execution plan, then there is not any benefit of this. Then you
> don't need prepared statements, and all this API is useless. So some
> questions are frequent and don't mean necessity to redesign. The developers
> just miss the fundamental knowledge of database technology.
>
> Regards
>
> Pavel
>
>
> Best regards.
>
> Alejandro Sánchez.
>
>
> El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió:
>
> Hi
>
> po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> Hello, some improvements in the prepared statements would facilitate
> their use from applications:
>
> - Use of table and column names in prepared statements.
>
> Example: select # from # where # = ?;
>
> - Use of arrays in prepared statements.
>
> Example: select # from article where id in (?);
>
> # = author,title
> ? = 10,24,45
>
>
> The server side prepared statements are based on reusing execution plans.
> You cannot reuse execution plans if you change table, or column. This is
> the reason why SQL identifiers are immutable in prepared statements. There
> are client side prepared statements - JDBC does it. There it is possible.
> But it is impossible on the server side. Prepared statements are like a
> compiled program. You can change parameters, variables - but you cannot
> change the program.
>
> Regards
>
> Pavel
>
>
>
>
>
> Best regards.
> Alejandro Sánchez.
>
>
>
>

I have already implemented this in my Java project with some kind of
SQL preprocessor. I leave the idea here in case more people are
interested and PostgreSQL developers findit convenient to include it.
It is just string concatenation but it is a sintactic sugar very useful
in any SQL application.
El lun, 01-03-2021 a las 17:35 +0100, Pavel Stehule escribió:
> Hi
>
> po 1. 3. 2021 v 17:26 odesílatel Alejandro Sánchez <
> alex(at)nexttypes(dot)com> napsal:
> > It is a matter of taste. I think this functionality would be better
> > in SQLand be the same for all languages without the need to use
> > string functions.
>
> You can try to implement it, and send a patch. But I think a) it will
> be just string concatenation, and then it is surely useless, or b)
> you should introduce a new parser, because current parser need to
> know SQL identifiers immediately. But anyway - anybody can write
> your opinion here. From me - I don't like this idea.
>
> Regards
> Pavel

Using any() has the disadvantage that in JDBC it is necessaryto create
an array with connection.createArrayOf() and indicatethe type of the
array, which complicates automation.
With statement.setObject() you can pass any type of parameter.JDBC
could add a method that doesn't need the array type.
String sql = "select author from article where id = any(?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setArray(1,
connection.createArrayOf("varchar", new String[] {"home",
"system"}));}
VS
query("select author from article where id = any(?)", new String[]
{"home", "system"});
El lun, 01-03-2021 a las 17:21 +0100, Pavel Stehule escribió:
> po 1. 3. 2021 v 17:15 odesílatel Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com> napsal:
> > po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <
> > alex(at)nexttypes(dot)com> napsal:
> > > The benefit is ease of use. One of the great advantages of
> > > prepared statements is nothaving to concatenate strings. The use
> > > of arrays would also be very useful.
> > > query("select " + column1 + "," + column2 from " " + table + "
> > > where id in (?), ids);
>
> The argument with arrays is not good. You can work with arrays just
> on binary level, that is more effective. But just you should use
> operator = ANY() instead IN.
>
> Regards
> Pavel

po 1. 3. 2021 v 21:35 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
napsal:

> Using any() has the disadvantage that in JDBC it is necessary
>
> to create an array with connection.createArrayOf() and indicate
>
> the type of the array, which complicates automation.
>
>
> With statement.setObject() you can pass any type of parameter.
>
> JDBC could add a method that doesn't need the array type.
>
>
> String sql = "select author from article where id = any(?)";
> try (PreparedStatement statement = connection.prepareStatement(sql)) {
> statement.setArray(1, connection.createArrayOf("varchar",
> new String[] {"home", "system"}));
> }
>
> VS
>
> query("select author from article where id = any(?)", new String[]
> {"home", "system"});
>

Can be, but this is a client side issue. It is about design of client side
API.

Pavel

> El lun, 01-03-2021 a las 17:21 +0100, Pavel Stehule escribió:
>
>
>
> po 1. 3. 2021 v 17:15 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> napsal:
>
>
>
> po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez <alex(at)nexttypes(dot)com>
> napsal:
>
> The benefit is ease of use. O
>
> ne of the great advantages of prepared statements is not
>
> having to concatenate strings. The use of arrays would also be very useful.
>
>
> query("select " + column1 + "," + column2 from " " + table + " where id in (?), ids);
>
>
>
>
> The argument with arrays is not good. You can work with arrays just on
> binary level, that is more effective. But just you should use operator =
> ANY() instead IN.
>
> Regards
>
> Pavel
>
>
>