Re: cursors with prepared statements

On Mon, Jul 16, 2018 at 8:56 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> 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.

Hmm. Your proposal to attach a USING clause to DECLARE .. CURSOR FOR
rather than inventing an OPEN command is an argument for a PostgreSQL
syntax extension, but your proposal to write DECLARE .. CURSOR FOR
rather than DECLARE .. CURSOR FOR EXECUTE is an argument for standard
syntax over and against a PostgreSQL extension.

That sounds a little contradictory, but I think I agree with it. If
we allow a USING clause for DECLARE .. CURSOR FOR, that doesn't
prevent somebody from inventing an OPEN command in the future. As
part of introducing such an OPEN command, DECLARE .. CURSOR FOR could
be made not to fail if the prepared statement has parameters but no
USING commands. On the other hand, if we insist on injecting the word
EXECUTE into the syntax as Heikki proposes, that's purely and simply
an incompatibility with the standard's syntax, as well as with what
ECPG already does.

So +1 for your position.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company