Re: cursors with prepared statements

On Fri, Jun 8, 2018 at 1:12 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> I have developed a patch that allows declaring cursors over prepared
> statements:
>
> DECLARE cursor_name CURSOR FOR prepared_statement_name
> [ USING param, param, ... ]
>
> This is an SQL standard feature. ECPG already supports it (with
> different internals).
>
> Internally, this just connects existing functionality in different ways,
> so it doesn't really introduce anything new.
>
> 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.
>
> 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?), and instead allowing
> EXECUTE + USING leads to a mess in the ECPG parser that exhausted me.
> So I'm leaving it as is for now and might give supporting EXECUTE +
> USING another try later on.
>

Sounds like a reasonable approach. Have you not considered using a
special OPEN syntax because there are some other forms of problems
with it?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com