From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Alejandro Sánchez <alex(at)nexttypes(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Improvements in prepared statements |
Date: | 2021-03-01 16:21:18 |
Message-ID: | CAFj8pRDxDeRGdThm-7zUKMtf0MLVR_HK252T8WBMMJuMyfcb8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
>>
>>
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Alejandro Sánchez | 2021-03-01 16:26:10 | Re: Improvements in prepared statements |
Previous Message | Pavel Stehule | 2021-03-01 16:15:02 | Re: Improvements in prepared statements |