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

Started by Pavel Stehule11 months ago14 messages

pavel.stehule@gmail.com

11 months ago

1 attachment(s)

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

Attachments:

v20250208-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchtext/x-patch; charset=US-ASCII; name=v20250208-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchDownload

From caa13851f6d8a04260dd6b6281da3647d348c8cb Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Sat, 8 Feb 2025 07:45:58 +0100
Subject: [PATCH] allow to use standard syntax for named arguments for plpgsql
 cursor arguments

---
 doc/src/sgml/plpgsql.sgml             |  3 ++-
 src/pl/plpgsql/src/pl_gram.y          |  4 ++--
 src/test/regress/expected/plpgsql.out | 19 +++++++++++++++++++
 src/test/regress/sql/plpgsql.sql      | 15 +++++++++++++++
 4 files changed, 38 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 78e4983139b..60af57712b7 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3317,7 +3317,7 @@ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING
      <title>Opening a Bound Cursor</title>
 
 <synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | => } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
 </synopsis>
 
          <para>
@@ -3352,6 +3352,7 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
 OPEN curs2;
 OPEN curs3(42);
 OPEN curs3(key := 42);
+OPEN curs3(Key => 42);
 </programlisting>
          </para>
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 64d2c362bf9..867017d8ed9 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3913,7 +3913,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, YYSTYPE *yylvalp, YYLTYPE *yyll
 
 		/* Check if it's a named parameter: "param := value" */
 		plpgsql_peek2(&tok1, &tok2, &arglocation, NULL, yyscanner);
-		if (tok1 == IDENT && tok2 == COLON_EQUALS)
+		if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 == EQUALS_GREATER))
 		{
 			char	   *argname;
 			IdentifierLookup save_IdentifierLookup;
@@ -3943,7 +3943,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, YYSTYPE *yylvalp, YYLTYPE *yyll
 			 * happen.
 			 */
 			tok2 = yylex(yylvalp, yyllocp, yyscanner);
-			if (tok2 != COLON_EQUALS)
+			if (tok2 != COLON_EQUALS && tok2 != EQUALS_GREATER)
 				yyerror(yyllocp, NULL, yyscanner, "syntax error");
 
 			any_named = true;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581bd..4549019f103 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2429,6 +2429,25 @@ select namedparmcursor_test9(6);
                      1
 (1 row)
 
+-- check standard syntax for named parameters
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int, debug int) for
+    select count(*) from tenk1 where thousand = p1 and tenthous = p2
+      and four = debug;
+  p2 int4 := 1006;
+  n int4;
+begin
+  open c1 (p1 => p1, p2 => p2, debug => 2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+select namedparmcursor_test9(6);
+ namedparmcursor_test9 
+-----------------------
+                     1
+(1 row)
+
 --
 -- tests for "raise" processing
 --
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 18c91572ae1..76da0a364c5 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2078,6 +2078,21 @@ begin
 end $$ language plpgsql;
 select namedparmcursor_test9(6);
 
+-- check standard syntax for named parameters
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int, debug int) for
+    select count(*) from tenk1 where thousand = p1 and tenthous = p2
+      and four = debug;
+  p2 int4 := 1006;
+  n int4;
+begin
+  open c1 (p1 => p1, p2 => p2, debug => 2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+select namedparmcursor_test9(6);
+
 --
 -- tests for "raise" processing
 --
-- 
2.48.1

Julien Rouhaud

rjuju123@gmail.com

11 months ago

In reply to: Pavel Stehule (#1)

Re: 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.

Japin Li

japinli@hotmail.com

11 months ago

In reply to: Julien Rouhaud (#2)

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

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

Pavel Stehule

pavel.stehule@gmail.com

11 months ago

In reply to: Japin Li (#3)

1 attachment(s)

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

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

Show quoted text

--
Regrads,
Japin Li

Attachments:

v20250208-2-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchtext/x-patch; charset=US-ASCII; name=v20250208-2-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchDownload

From 3eb6e29eacbe2ad1dd0d2b7847ca1eccde71594f Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Sat, 8 Feb 2025 07:45:58 +0100
Subject: [PATCH] allow to use standard syntax for named arguments for plpgsql
 cursor arguments

---
 doc/src/sgml/plpgsql.sgml             |  3 ++-
 src/pl/plpgsql/src/pl_gram.y          | 13 ++++++++-----
 src/test/regress/expected/plpgsql.out | 19 +++++++++++++++++++
 src/test/regress/sql/plpgsql.sql      | 15 +++++++++++++++
 4 files changed, 44 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 78e4983139b..60af57712b7 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3317,7 +3317,7 @@ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING
      <title>Opening a Bound Cursor</title>
 
 <synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | => } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
 </synopsis>
 
          <para>
@@ -3352,6 +3352,7 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
 OPEN curs2;
 OPEN curs3(42);
 OPEN curs3(key := 42);
+OPEN curs3(Key => 42);
 </programlisting>
          </para>
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 64d2c362bf9..618a97b64f2 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3911,9 +3911,12 @@ 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)
+		if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 == EQUALS_GREATER))
 		{
 			char	   *argname;
 			IdentifierLookup save_IdentifierLookup;
@@ -3939,11 +3942,11 @@ 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
-			 * happen.
+			 * Eat the ":=" and the "=>". We already peeked, so the error should
+			 * never happen.
 			 */
 			tok2 = yylex(yylvalp, yyllocp, yyscanner);
-			if (tok2 != COLON_EQUALS)
+			if (tok2 != COLON_EQUALS && tok2 != EQUALS_GREATER)
 				yyerror(yyllocp, NULL, yyscanner, "syntax error");
 
 			any_named = true;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581bd..4549019f103 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2429,6 +2429,25 @@ select namedparmcursor_test9(6);
                      1
 (1 row)
 
+-- check standard syntax for named parameters
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int, debug int) for
+    select count(*) from tenk1 where thousand = p1 and tenthous = p2
+      and four = debug;
+  p2 int4 := 1006;
+  n int4;
+begin
+  open c1 (p1 => p1, p2 => p2, debug => 2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+select namedparmcursor_test9(6);
+ namedparmcursor_test9 
+-----------------------
+                     1
+(1 row)
+
 --
 -- tests for "raise" processing
 --
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 18c91572ae1..76da0a364c5 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2078,6 +2078,21 @@ begin
 end $$ language plpgsql;
 select namedparmcursor_test9(6);
 
+-- check standard syntax for named parameters
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int, debug int) for
+    select count(*) from tenk1 where thousand = p1 and tenthous = p2
+      and four = debug;
+  p2 int4 := 1006;
+  n int4;
+begin
+  open c1 (p1 => p1, p2 => p2, debug => 2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+select namedparmcursor_test9(6);
+
 --
 -- tests for "raise" processing
 --
-- 
2.48.1

Tom Lane

tgl@sss.pgh.pa.us

11 months ago

In reply to: Pavel Stehule (#1)

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

11 months ago

In reply to: Tom Lane (#5)

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

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

Show quoted text

regards, tom lane

Tom Lane

tgl@sss.pgh.pa.us

11 months ago

In reply to: Pavel Stehule (#6)

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

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

Pavel Stehule

pavel.stehule@gmail.com

11 months ago

In reply to: Tom Lane (#7)

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

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

Show quoted text

regards, tom lane

Pavel Stehule

pavel.stehule@gmail.com

11 months ago

In reply to: Tom Lane (#7)

1 attachment(s)

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

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

Show quoted text

regards, tom lane

Attachments:

v20250211-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchtext/x-patch; charset=US-ASCII; name=v20250211-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchDownload

From 938bd5007b62c854e423b696ac42c680fa28178e Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Sat, 8 Feb 2025 07:45:58 +0100
Subject: [PATCH] allow to use standard syntax for named arguments for plpgsql
 cursor arguments

---
 doc/src/sgml/plpgsql.sgml             |  3 ++-
 src/pl/plpgsql/src/pl_gram.y          | 13 ++++++++-----
 src/test/regress/expected/plpgsql.out |  3 ++-
 src/test/regress/sql/plpgsql.sql      |  3 ++-
 4 files changed, 14 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 78e4983139b..60af57712b7 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3317,7 +3317,7 @@ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING
      <title>Opening a Bound Cursor</title>
 
 <synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | => } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
 </synopsis>
 
          <para>
@@ -3352,6 +3352,7 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
 OPEN curs2;
 OPEN curs3(42);
 OPEN curs3(key := 42);
+OPEN curs3(Key => 42);
 </programlisting>
          </para>
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 64d2c362bf9..618a97b64f2 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3911,9 +3911,12 @@ 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)
+		if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 == EQUALS_GREATER))
 		{
 			char	   *argname;
 			IdentifierLookup save_IdentifierLookup;
@@ -3939,11 +3942,11 @@ 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
-			 * happen.
+			 * Eat the ":=" and the "=>". We already peeked, so the error should
+			 * never happen.
 			 */
 			tok2 = yylex(yylvalp, yyllocp, yyscanner);
-			if (tok2 != COLON_EQUALS)
+			if (tok2 != COLON_EQUALS && tok2 != EQUALS_GREATER)
 				yyerror(yyllocp, NULL, yyscanner, "syntax error");
 
 			any_named = true;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581bd..31aa806e491 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2419,7 +2419,8 @@ declare
   p2 int4 := 1006;
   n int4;
 begin
-  open c1 (p1 := p1, p2 := p2, debug := 2);
+  -- use both supported syntaxes for named arguments
+  open c1 (p1 := p1, p2 => p2, debug => 2);
   fetch c1 into n;
   return n;
 end $$ language plpgsql;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 18c91572ae1..aaae1e44c6f 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2072,7 +2072,8 @@ declare
   p2 int4 := 1006;
   n int4;
 begin
-  open c1 (p1 := p1, p2 := p2, debug := 2);
+  -- use both supported syntaxes for named arguments
+  open c1 (p1 := p1, p2 => p2, debug => 2);
   fetch c1 into n;
   return n;
 end $$ language plpgsql;
-- 
2.48.1

#10

Gilles Darold

gilles@darold.net

11 months ago

In reply to: Pavel Stehule (#9)

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

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/

#11

Pavel Stehule

pavel.stehule@gmail.com

11 months ago

In reply to: Gilles Darold (#10)

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

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

Show quoted text

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

#12

Pavel Stehule

pavel.stehule@gmail.com

11 months ago

In reply to: Pavel Stehule (#11)

2 attachment(s)

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

ú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

Show quoted text

Best regards,

Thank you for review

Pavel

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

Attachments:

v20250227-0002-Separate-old-proprietary-syntax-to-own-para-with-not.patchtext/x-patch; charset=US-ASCII; name=v20250227-0002-Separate-old-proprietary-syntax-to-own-para-with-not.patchDownload

From aa39b0ad4fe87c33ae889bbcfa9a81e973605fd8 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Thu, 27 Feb 2025 16:35:52 +0100
Subject: [PATCH 2/2] Separate old (proprietary) syntax to own para with note
 so it is supported just for compatibility reasons.

---
 doc/src/sgml/plpgsql.sgml | 13 ++++++++++---
 1 file changed, 10 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 60af57712b7..2acdf43b4a1 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3317,7 +3317,7 @@ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING
      <title>Opening a Bound Cursor</title>
 
 <synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | => } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { => | := } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
 </synopsis>
 
          <para>
@@ -3340,7 +3340,7 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
           Argument values can be passed using either <firstterm>positional</firstterm>
           or <firstterm>named</firstterm> notation.  In positional
           notation, all arguments are specified in order.  In named notation,
-          each argument's name is specified using <literal>:=</literal> to
+          each argument's name is specified using <literal>=></literal> to
           separate it from the argument expression. Similar to calling
           functions, described in <xref linkend="sql-syntax-calling-funcs"/>, it
           is also allowed to mix positional and named notation.
@@ -3351,11 +3351,18 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
 <programlisting>
 OPEN curs2;
 OPEN curs3(42);
-OPEN curs3(key := 42);
 OPEN curs3(Key => 42);
 </programlisting>
          </para>
 
+         <para>
+          An older syntax based on <literal>:=</literal> is supported for backward
+          compatibility:
+<programlisting>
+OPEN curs3(key := 42);
+</programlisting>
+         </para>
+
          <para>
           Because variable substitution is done on a bound cursor's query,
           there are really two ways to pass values into the cursor: either
-- 
2.48.1

v20250227-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchtext/x-patch; charset=US-ASCII; name=v20250227-0001-allow-to-use-standard-syntax-for-named-arguments-for.patchDownload

From 0ac81502c81c9359b7f9ecbd77e5edd34f410a15 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Sat, 8 Feb 2025 07:45:58 +0100
Subject: [PATCH 1/2] allow to use standard syntax for named arguments for
 plpgsql cursor arguments

---
 doc/src/sgml/plpgsql.sgml             |  3 ++-
 src/pl/plpgsql/src/pl_gram.y          | 13 ++++++++-----
 src/test/regress/expected/plpgsql.out |  3 ++-
 src/test/regress/sql/plpgsql.sql      |  3 ++-
 4 files changed, 14 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 78e4983139b..60af57712b7 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3317,7 +3317,7 @@ OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING
      <title>Opening a Bound Cursor</title>
 
 <synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | => } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
 </synopsis>
 
          <para>
@@ -3352,6 +3352,7 @@ OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replace
 OPEN curs2;
 OPEN curs3(42);
 OPEN curs3(key := 42);
+OPEN curs3(Key => 42);
 </programlisting>
          </para>
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 8048e040f81..dc0ae113184 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -3955,9 +3955,12 @@ 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)
+		if (tok1 == IDENT && (tok2 == COLON_EQUALS || tok2 == EQUALS_GREATER))
 		{
 			char	   *argname;
 			IdentifierLookup save_IdentifierLookup;
@@ -3983,11 +3986,11 @@ 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
-			 * happen.
+			 * Eat the ":=" and the "=>". We already peeked, so the error should
+			 * never happen.
 			 */
 			tok2 = yylex(yylvalp, yyllocp, yyscanner);
-			if (tok2 != COLON_EQUALS)
+			if (tok2 != COLON_EQUALS && tok2 != EQUALS_GREATER)
 				yyerror(yyllocp, NULL, yyscanner, "syntax error");
 
 			any_named = true;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581bd..31aa806e491 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2419,7 +2419,8 @@ declare
   p2 int4 := 1006;
   n int4;
 begin
-  open c1 (p1 := p1, p2 := p2, debug := 2);
+  -- use both supported syntaxes for named arguments
+  open c1 (p1 := p1, p2 => p2, debug => 2);
   fetch c1 into n;
   return n;
 end $$ language plpgsql;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 18c91572ae1..aaae1e44c6f 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2072,7 +2072,8 @@ declare
   p2 int4 := 1006;
   n int4;
 begin
-  open c1 (p1 := p1, p2 := p2, debug := 2);
+  -- use both supported syntaxes for named arguments
+  open c1 (p1 := p1, p2 => p2, debug => 2);
   fetch c1 into n;
   return n;
 end $$ language plpgsql;
-- 
2.48.1

#13

Tom Lane

tgl@sss.pgh.pa.us

10 months ago

In reply to: Pavel Stehule (#12)

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

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

#14

Pavel Stehule

pavel.stehule@gmail.com

10 months ago

In reply to: Tom Lane (#13)

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

ú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

Show quoted text

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