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

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@gmail.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@hotmail.com> napsal:

On Sat, 08 Feb 2025 at 16:34, Julien Rouhaud <rjuju123@gmail.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

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

Hi

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

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.

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

/messages/by-id/20091008023926.1BE85753FB7@cvs.postgresql.org
(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

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

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

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?

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

Hi

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

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?

I changed regress tests like you proposed

Regards

Pavel

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

Hi

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

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?

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

Regards

Pavel

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

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

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

Thank you very much

Regards

Pavel