Re: cursors with prepared statements

On Fri, Jun 8, 2018 at 1:12 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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