proposal - plpgsql - support standard syntax for named arguments for cursors

Hi

when I worked on strict expr check patch I found so syntax for named
arguments of cursors supports only our legacy proprietary syntax `argname
:= value`

https://www.postgresql.org/docs/current/plpgsql-cursors.html

I propose to enhancing to ANSI/SQL standard syntax for named arguments
`argname => value`

The patch is almost trivial

Regards

Pavel

Hi,

On Sat, Feb 08, 2025 at 07:47:23AM +0100, Pavel Stehule wrote:
> Hi
>
> when I worked on strict expr check patch I found so syntax for named
> arguments of cursors supports only our legacy proprietary syntax `argname
> := value`
>
> https://www.postgresql.org/docs/current/plpgsql-cursors.html
>
> I propose to enhancing to ANSI/SQL standard syntax for named arguments
> `argname => value`

Seems sensible to me.

> The patch is almost trivial

Documentation and tests are updated, and the patch LGTM.

On Sat, 08 Feb 2025 at 16:34, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> Hi,
>
> On Sat, Feb 08, 2025 at 07:47:23AM +0100, Pavel Stehule wrote:
>> Hi
>>
>> when I worked on strict expr check patch I found so syntax for named
>> arguments of cursors supports only our legacy proprietary syntax `argname
>> := value`
>>
>> https://www.postgresql.org/docs/current/plpgsql-cursors.html
>>
>> I propose to enhancing to ANSI/SQL standard syntax for named arguments
>> `argname => value`
>
> Seems sensible to me.
>
>> The patch is almost trivial
>
> Documentation and tests are updated, and the patch LGTM.

Maybe we should also update the comments?

diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 867017d8ed9..43186c8e85e 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3911,7 +3911,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, YYSTYPE *yylvalp, YYLTYPE *yyll
tok2;
int arglocation;

- /* Check if it's a named parameter: "param := value" */
+ /* Check if it's a named parameter: "param := value" or "param => value" */
plpgsql_peek2(&tok1, &tok2, &arglocation, NULL, yyscanner);
if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 == EQUALS_GREATER))
{
@@ -3939,7 +3939,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, YYSTYPE *yylvalp, YYLTYPE *yyll
parser_errposition(*yyllocp)));

/*
- * Eat the ":=". We already peeked, so the error should never
+ * Eat the ":=" and "=>". We already peeked, so the error should never
* happen.
*/
tok2 = yylex(yylvalp, yyllocp, yyscanner);
--
Regrads,
Japin Li

Hi

so 8. 2. 2025 v 11:27 odesílatel Japin Li <japinli(at)hotmail(dot)com> napsal:

> On Sat, 08 Feb 2025 at 16:34, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> > Hi,
> >
> > On Sat, Feb 08, 2025 at 07:47:23AM +0100, Pavel Stehule wrote:
> >> Hi
> >>
> >> when I worked on strict expr check patch I found so syntax for named
> >> arguments of cursors supports only our legacy proprietary syntax
> `argname
> >> := value`
> >>
> >> https://www.postgresql.org/docs/current/plpgsql-cursors.html
> >>
> >> I propose to enhancing to ANSI/SQL standard syntax for named arguments
> >> `argname => value`
> >
> > Seems sensible to me.
> >
> >> The patch is almost trivial
> >
> > Documentation and tests are updated, and the patch LGTM.
>
> Maybe we should also update the comments?
>
> diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
> index 867017d8ed9..43186c8e85e 100644
> --- a/src/pl/plpgsql/src/pl_gram.y
> +++ b/src/pl/plpgsql/src/pl_gram.y
> @@ -3911,7 +3911,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until,
> YYSTYPE *yylvalp, YYLTYPE *yyll
> tok2;
> int arglocation;
>
> - /* Check if it's a named parameter: "param := value" */
> + /* Check if it's a named parameter: "param := value" or
> "param => value" */
> plpgsql_peek2(&tok1, &tok2, &arglocation, NULL, yyscanner);
> if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 ==
> EQUALS_GREATER))
> {
> @@ -3939,7 +3939,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until,
> YYSTYPE *yylvalp, YYLTYPE *yyll
>
> parser_errposition(*yyllocp)));
>
> /*
> - * Eat the ":=". We already peeked, so the error
> should never
> + * Eat the ":=" and "=>". We already peeked, so
> the error should never
> * happen.
> */
> tok2 = yylex(yylvalp, yyllocp, yyscanner);
>

good idea

done

Regards

Pavel

> --
> Regrads,
> Japin Li
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

Hi

so 8. 2. 2025 v 20:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>

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.

This is from patch
https://github.com/postgres/postgres/commit/4adead1d224278ff3064636063a818eba17cb211

It is from the window, when the named arguments was supported already

https://www.postgresql.org/message-id/[email protected]
(the syntax was changed before release)
but not with ANSI syntax
https://github.com/postgres/postgres/commit/865f14a2d31af23a05bbf2df04c274629c5d5c4d

I forgot to fix this in my patch for 9.5 - probably I missed this
functionality

Regards

Pavel

>
> regards, tom lane
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> so 8. 2. 2025 v 20:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

so 8. 2. 2025 v 22:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > so 8. 2. 2025 v 20:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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?
>

just aesthetic reasons - it looks strange for me to mix two styles in one
code.
But in this very simple case - it is not important.
please, modify tests how you like

Regards

Pavel

>
> regards, tom lane
>

Hi

so 8. 2. 2025 v 22:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > so 8. 2. 2025 v 20:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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?
>
>
I changed regress tests like you proposed

Regards

Pavel

> 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(at)darold(dot)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/
>
>

Hi

út 25. 2. 2025 v 6:32 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> po 24. 2. 2025 v 21:05 odesílatel Gilles Darold <gilles(at)darold(dot)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?
>>
>
I modified doc in same manner like function's named arguments are described

Regards

Pavel

>
>>
>> Best regards,
>>
>
> Thank you for review
>
> Pavel
>
>
>>
>> --
>> Gilles Darold
>> http://www.darold.net/
>>
>>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> po 24. 2. 2025 v 21:05 odesílatel Gilles Darold <gilles(at)darold(dot)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

út 4. 3. 2025 v 0:04 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> >> po 24. 2. 2025 v 21:05 odesílatel Gilles Darold <gilles(at)darold(dot)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
>

Thank you very much

Regards

Pavel

>
> 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
>