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.
When looking at the patch, some parts will look easier through git diff -w.
And the changes in the ECPG parser are needed because ECPG already
supported that syntax separately, but now it needs to override the rules
from the main parser instead. That stuff has test coverage, fortunately.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services