Thread: Re: proposal - plpgsql - support standard syntax for named arguments for cursors

Pavel Stehule <pavel.stehule@gmail.com> writes:
> I propose to enhancing to ANSI/SQL standard syntax for named arguments
> `argname => value`

Is there any reason to think that that's actually in the standard?
I poked around in SQL:2021 a little and couldn't find anything about
cursors with arguments at all.

                        regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> so 8. 2. 2025 v 20:25 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> Is there any reason to think that that's actually in the standard?

> I think the possibility to use named arguments in OPEN statements is a
> PostgreSQL proprietary feature.
> And usage of cursors in PL/pgSQL is based on PL/SQL (not on SQL/PSM from
> standard), but named
> arguments for cursor is PostgreSQL proprietary feature and the syntax based
> on usage `:=` is our
> proprietary too.

Hmm ... yeah, it's not in SQL/PSM, but looking at PL/SQL:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/OPEN-statement.html

I see

    You can specify actual cursor parameters with either
    positional notation or named notation. For information about
    these notations, see "Positional, Named, and Mixed Notation
    for Actual Parameters".

and that link blesses the use of "name => value" (and not ":=").
So agreed, we should adjust this.

Is there a reason we need a whole new test case instead of
tweaking one of the existing ones?

                        regards, tom lane

Review:

This patch claims to add SQL/PSM named arguments syntax to cursors and
this what it does exactly.

  It compiles without error with master current code and all tests
passed successfully.

I think it could be ready to be committed.


Note for the committer: does it make sense to mention in the
documentation that this standard SQL/PSM syntax is preferred than the PG
syntax?

Best regards,

--
Gilles Darold
http://www.darold.net/

Hi

po 24. 2. 2025 v 21:05 odesílatel Gilles Darold <gilles@darold.net> napsal:

Review:

This patch claims to add SQL/PSM named arguments syntax to cursors and
this what it does exactly.

  It compiles without error with master current code and all tests
passed successfully.

I think it could be ready to be committed.


Note for the committer: does it make sense to mention in the
documentation that this standard SQL/PSM syntax is preferred than the PG
syntax?

Best regards,

Thank you for review

Pavel

 

--
Gilles Darold
http://www.darold.net/

Pavel Stehule <pavel.stehule@gmail.com> writes:
>> po 24. 2. 2025 v 21:05 odesílatel Gilles Darold <gilles@darold.net>
>> napsal:
>>> I think it could be ready to be committed.

Pushed with a docs/test correction: this also affects the syntax
of FOR-over-cursor.

>>> Note for the committer: does it make sense to mention in the
>>> documentation that this standard SQL/PSM syntax is preferred than the PG
>>> syntax?

> I modified doc in same manner like function's named arguments are described

I didn't especially care for this change and didn't include it.  We've
had the := syntax for decades and aren't likely to ever remove it,
so why start acting like it's deprecated?

                        regards, tom lane