On 07/06/18 22:42, Peter Eisentraut 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.
Hmm. I'm not excited about adding PostgreSQL-extensions to the SQL
standard. 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.
> 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)
? As a user, I'm already familiar with the "EXECUTE p (foo, bar)"
syntax, so that's what I would intuitively try to use with DECLARE as
well. In fact, I think I tried doing just that once, and was
disappointed that it didn't work.
> 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.
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.
This also adds a test case for the existing "EXECUTE <stmt> (<params>)"
syntax in ECPG. The current ECPG parsing of that is actually a bit
weird, it allows "EXECUTE stmt (:param1) USING :param2", which seems
unintentional. This patch rejects that syntax.
- Heikki