Horton

Thread

Commits

  1. Allow => syntax for named cursor arguments in plpgsql.

  2. Allow named parameters to be specified using => in addition to :=

  3. Add support for passing cursor parameters in named notation in PL/pgSQL.

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-08T06:47:23Z

    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

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

    Julien Rouhaud <rjuju123@gmail.com> — 2025-02-08T08:34:35Z

    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.

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

    Japin Li <japinli@hotmail.com> — 2025-02-08T10:26:54Z

    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

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-08T11:28:34Z

    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


> --
> Regrads,
> Japin Li
>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-08T19:25:58Z

    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

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-08T19:56:28Z

    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

https://www.postgresql.org/message-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



>
>                         regards, tom lane
>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-08T21:25:18Z

    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

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-09T05:22:17Z

    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


>
>                         regards, tom lane
>

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-11T13:41:18Z

    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


>                         regards, tom lane
>

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

    Gilles DAROLD <gilles@darold.net> — 2025-02-24T20:05:12Z

    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. Re: proposal - plpgsql - support standard syntax for named arguments for cursors

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-25T05:32:37Z

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

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-02-27T15:40:18Z

    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


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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-03T23:04:31Z

    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. Re: proposal - plpgsql - support standard syntax for named arguments for cursors

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-03-04T05:34:14Z

    ú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


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