Re: cursors with prepared statements

On 11.07.18 19:07, Heikki Linnakangas wrote:
>> One point worth pondering is how to pass the parameters of the prepared
>> statements. The actual SQL standard syntax would be
>>
>> DECLARE cursor_name CURSOR FOR prepared_statement_name;
>> OPEN cursor_name USING param, param;
>>
>> But since we don't have the OPEN statement in direct SQL, it made sense
>> to me to attach the USING clause directly to the DECLARE statement.
>
> Hmm. I'm not excited about adding PostgreSQL-extensions to the SQL
> standard.

Isn't that what we do all the time?

> It's confusing, and risks conflicting with future additions to
> the standard. ECPG supports the actual standard syntax, with OPEN,
> right? So this wouldn't be consistent with ECPG, either.

It would be consistent for the case of no parameters.

>> Curiously, the direct EXECUTE statement uses the non-standard syntax
>>
>> EXECUTE prep_stmt (param, param);
>>
>> instead of the standard
>>
>> EXECUTE prep_stmt USING param, param;
>>
>> I tried to consolidate this. But using
>>
>> DECLARE c CURSOR FOR p (foo, bar)
>>
>> leads to parsing conflicts (and looks confusing?),
>
> How about
>
> DECLARE c CURSOR FOR EXECUTE p (foo, bar)

That's not the standard syntax for the case of no parameters.

> The attached patch seems to do the trick, of allowing EXECUTE + USING.
> I'm not sure this is worth the trouble, though, since EXECUTE as a plain
> SQL command is a PostgreSQL-extension anyway.

I think it's a PostgreSQL extension that we allow just about anything to
be executed directly. So we should still use the standard syntax either
way. It would be weird if EXECUTE or any other command had different
syntax in direct SQL, ECPG, PL/pgSQL, etc. We have some differences
already, but we shouldn't create more.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services