Thread: pl/pgsql feature request: shorthand for argument and local variable references

pl/pgsql feature request: shorthand for argument and local variable references

From

Jack Christensen

Date:

17 November 2020, 21:04:24

When arguments and other local variables in pl/pgsql functions have the same name as columns referenced in queries it is necessary to disambiguate the names. This can be done by prefixing the function name (e.g. my_func.name), using the argument number is the case of an argument (e.g. $1), or renaming the variable (e.g. _name). It is also possible to use a GUC to always use the variable or the column but that seems dangerous to me.

Prefixing names with an underscore works well enough for local variables, but when using named arguments I prefer the external name not require an underscore. I would like to suggest a standard prefix such as $ to reference a local variable or argument. $ followed by an integer already references an argument by ordinal. What if $ followed by a name meant a local reference (essentially it would expand to "my_func.")?

For example, currently I have to do something like this:

create function update_item(id int, foo int, bar text) returns void
language plpgsql as $$
begin
update items
set foo = update_item.foo,
bar = update_item.bar
where items.id = update_item.id;
end;
$$;

I would like to be able to do something like:

create function update_item(id int, foo int, bar text) returns void
language plpgsql as $$
begin
update items
set foo = $foo,
bar = $bar
where items.id = $id;
end;
$$;

Any opinions on the desirability of this feature? My C skills are rather atrophied, but from the outside it seems like a small enough change I might be able to tackle it...

Jack

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

17 November 2020, 22:35:33

út 17. 11. 2020 v 19:04 odesílatel Jack Christensen <jack@jncsoftware.com> napsal:

When arguments and other local variables in pl/pgsql functions have the same name as columns referenced in queries it is necessary to disambiguate the names. This can be done by prefixing the function name (e.g. my_func.name), using the argument number is the case of an argument (e.g. $1), or renaming the variable (e.g. _name). It is also possible to use a GUC to always use the variable or the column but that seems dangerous to me.

Prefixing names with an underscore works well enough for local variables, but when using named arguments I prefer the external name not require an underscore. I would like to suggest a standard prefix such as $ to reference a local variable or argument. $ followed by an integer already references an argument by ordinal. What if $ followed by a name meant a local reference (essentially it would expand to "my_func.")?

For example, currently I have to do something like this:

create function update_item(id int, foo int, bar text) returns void
language plpgsql as $$
begin
update items
set foo = update_item.foo,
bar = update_item.bar
where items.id = update_item.id;
end;
$$;

I would like to be able to do something like:

create function update_item(id int, foo int, bar text) returns void
language plpgsql as $$
begin
update items
set foo = $foo,
bar = $bar
where items.id = $id;
end;
$$;

Any opinions on the desirability of this feature? My C skills are rather atrophied, but from the outside it seems like a small enough change I might be able to tackle it...

I don't like this proposal too much. Introducing the next different syntax for writing local variables doesn't look like a good idea for me. More this syntax is used by very different languages than is PL/pgSQL, and then it can be messy. The behaviour of local variables in PHP or Perl or shell is really very different.

Personally in your example I very much like notation "update_item.id", because there is a clean signal so "id" is the function's argument. When you use "$id", then it is not clean if "id" is a local variable or function's argument. So your proposal decreases safety :-/. Plus this syntax reduces collision only on one side, you should use aliases for sql identifiers and again it is not balanced - In MS SQL I can write predicate id = @id. But it is not possible in your proposal (and it is not possible from compatibility reasons ever).

More we already has a possibility to do ALIAS of any variable https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS

I understand that there can be problems with functions with very long names.

We already can do

CREATE OR REPLACE FUNCTION public.fx(par1 integer, par2 integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
<<b>>
declare
p1 alias for par1;
p2 alias for par2;
begin
raise notice '% % % %', par1, par2, b.p1, b.p2;
end;
$function$

or safer

CREATE OR REPLACE FUNCTION public.fx(par1 integer, par2 integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
<<b>>
declare
p1 alias for fx.par1;
p2 alias for fx.par2;
begin
raise notice '% % % %', par1, par2, b.p1, b.p2;
end;
$function$

So I think introducing new syntax is not necessary. The open question is a possibility to do aliasing more comfortably. ADA language has a possibility to rename function or procedure. But it is much more stronger, than can be implemented in plpgsql. Probably the most easy implementation can be a possibility to specify a new argument's label with already supported #option syntax.

CREATE OR REPLACE FUNCTION very_long_name(par1 int)

RETURNS int AS $$

#routine_label lnm

BEGIN

RAISE NOTICE '%', lnm.par1;

Regards

Pavel

Jack

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Jack Christensen

Date:

17 November 2020, 23:18:51

On Tue, Nov 17, 2020 at 1:36 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Personally in your example I very much like notation "update_item.id", because there is a clean signal so "id" is the function's argument. When you use "$id", then it is not clean if "id" is a local variable or function's argument. So your proposal decreases safety :-/. Plus this syntax reduces collision only on one side, you should use aliases for sql identifiers and again it is not balanced - In MS SQL I can write predicate id = @id. But it is not possible in your proposal (and it is not possible from compatibility reasons ever).

More we already has a possibility to do ALIAS of any variable https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS

I understand that there can be problems with functions with very long names.

Right. The problem is most pronounced when the function name is long. And in particular when there are a lot of optional arguments. In this case the caller will be using named arguments so I want to avoid prefixing the parameter names. And while alias is an option, that can also get quite verbose when there are a large number of parameters.

So I think introducing new syntax is not necessary. The open question is a possibility to do aliasing more comfortably. ADA language has a possibility to rename function or procedure. But it is much more stronger, than can be implemented in plpgsql. Probably the most easy implementation can be a possibility to specify a new argument's label with already supported #option syntax.

CREATE OR REPLACE FUNCTION very_long_name(par1 int)
RETURNS int AS $$
#routine_label lnm
BEGIN
RAISE NOTICE '%', lnm.par1;

I concur. This would be an improvement.

Jack

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Chapman Flack

Date:

17 November 2020, 23:45:11

On 11/17/20 15:18, Jack Christensen wrote:
>> CREATE OR REPLACE FUNCTION very_long_name(par1 int)
>> RETURNS int AS $$
>> #routine_label lnm
>> BEGIN
>>   RAISE NOTICE '%', lnm.par1;

Could that be somehow shoehorned into the existing ALIAS syntax, maybe as

DECLARE
  lnm ALIAS FOR ALL very_long_name.*;

or something?

(And would it be cool if Table C.1 [1] had some sort of javascript-y
filtering on reservedness categories, for just such kinds of bikeshedding?)

Regards,
-Chap


[1] https://www.postgresql.org/docs/13/sql-keywords-appendix.html#KEYWORDS-TABLE

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 November 2020, 08:58:21

út 17. 11. 2020 v 21:45 odesílatel Chapman Flack <chap@anastigmatix.net> napsal:

On 11/17/20 15:18, Jack Christensen wrote:
>> CREATE OR REPLACE FUNCTION very_long_name(par1 int)
>> RETURNS int AS $$
>> #routine_label lnm
>> BEGIN
>> RAISE NOTICE '%', lnm.par1;

Could that be somehow shoehorned into the existing ALIAS syntax, maybe as

DECLARE
lnm ALIAS FOR ALL very_long_name.*;

or something?

I thought about it - but I don't think so this syntax is correct - in your case it should be

lnm.* ALIAS FOR ALL very_long_name.*;

but it looks a little bit scary in ADA based language.

Maybe

DECLARE lnm LABEL ALIAS FOR very_long_name;

DECLARE lnm ALIAS FOR LABEL very_long_name;

I think so implementation should not be hard. But there are advantages, disadvantages - 1. label aliasing increases the complexity of variable searching (instead comparing string with name of namespace, you should compare list of names). Probably not too much. 2. The syntax is a little bit harder than #option. Implementation based on #option can just rename top namespace, so there is not any overhead, and parsing #option syntax is pretty simple (and the syntax is shorter). So from implementation reasons I prefer #option based syntax. There is clear zero impact on performance.

Regards

Pavel

(And would it be cool if Table C.1 [1] had some sort of javascript-y
filtering on reservedness categories, for just such kinds of bikeshedding?)

Regards,
-Chap

[1] https://www.postgresql.org/docs/13/sql-keywords-appendix.html#KEYWORDS-TABLE

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 November 2020, 23:21:06

st 18. 11. 2020 v 6:58 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

út 17. 11. 2020 v 21:45 odesílatel Chapman Flack <chap@anastigmatix.net> napsal:
On 11/17/20 15:18, Jack Christensen wrote:
>> CREATE OR REPLACE FUNCTION very_long_name(par1 int)
>> RETURNS int AS $$
>> #routine_label lnm
>> BEGIN
>> RAISE NOTICE '%', lnm.par1;

Could that be somehow shoehorned into the existing ALIAS syntax, maybe as

DECLARE
lnm ALIAS FOR ALL very_long_name.*;

or something?

I thought about it - but I don't think so this syntax is correct - in your case it should be

lnm.* ALIAS FOR ALL very_long_name.*;

but it looks a little bit scary in ADA based language.

Maybe

DECLARE lnm LABEL ALIAS FOR very_long_name;

or

DECLARE lnm ALIAS FOR LABEL very_long_name;

I think so implementation should not be hard. But there are advantages, disadvantages - 1. label aliasing increases the complexity of variable searching (instead comparing string with name of namespace, you should compare list of names). Probably not too much. 2. The syntax is a little bit harder than #option. Implementation based on #option can just rename top namespace, so there is not any overhead, and parsing #option syntax is pretty simple (and the syntax is shorter). So from implementation reasons I prefer #option based syntax. There is clear zero impact on performance.

Regards

I checked code - and I have to change my opinion - the current implementation of namespaces in plpgsql doesn't allow renaming or realising labels elegantly. The design has low memory requirements but it is not flexible. I wrote a proof concept patch, and I had to hack the nsitem little bit.

postgres=# create or replace function bubu(a int, b int)
returns void as $$

#routine_label b
begin
raise notice '% %', b.a, b.b;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select bubu(10,20);
NOTICE: 10 20
┌──────┐
│ bubu │
╞══════╡
│ │
└──────┘
(1 row)

Pavel

(And would it be cool if Table C.1 [1] had some sort of javascript-y
filtering on reservedness categories, for just such kinds of bikeshedding?)

Regards,
-Chap

[1] https://www.postgresql.org/docs/13/sql-keywords-appendix.html#KEYWORDS-TABLE

Attachment

plpgsql-routine_label-option.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 November 2020, 23:24:48

st 18. 11. 2020 v 21:21 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

st 18. 11. 2020 v 6:58 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

út 17. 11. 2020 v 21:45 odesílatel Chapman Flack <chap@anastigmatix.net> napsal:
On 11/17/20 15:18, Jack Christensen wrote:
>> CREATE OR REPLACE FUNCTION very_long_name(par1 int)
>> RETURNS int AS $$
>> #routine_label lnm
>> BEGIN
>> RAISE NOTICE '%', lnm.par1;

Could that be somehow shoehorned into the existing ALIAS syntax, maybe as

DECLARE
lnm ALIAS FOR ALL very_long_name.*;

or something?

I thought about it - but I don't think so this syntax is correct - in your case it should be

lnm.* ALIAS FOR ALL very_long_name.*;

but it looks a little bit scary in ADA based language.

Maybe

DECLARE lnm LABEL ALIAS FOR very_long_name;

or

DECLARE lnm ALIAS FOR LABEL very_long_name;

I think so implementation should not be hard. But there are advantages, disadvantages - 1. label aliasing increases the complexity of variable searching (instead comparing string with name of namespace, you should compare list of names). Probably not too much. 2. The syntax is a little bit harder than #option. Implementation based on #option can just rename top namespace, so there is not any overhead, and parsing #option syntax is pretty simple (and the syntax is shorter). So from implementation reasons I prefer #option based syntax. There is clear zero impact on performance.

Regards

I checked code - and I have to change my opinion - the current implementation of namespaces in plpgsql doesn't allow renaming or realising labels elegantly. The design has low memory requirements but it is not flexible. I wrote a proof concept patch, and I had to hack the nsitem little bit.

postgres=# create or replace function bubu(a int, b int)
returns void as $$
#routine_label b

maybe a better name for this option can be "routine_alias".

begin
raise notice '% %', b.a, b.b;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select bubu(10,20);
NOTICE: 10 20
┌──────┐
│ bubu │
╞══════╡
│ │
└──────┘
(1 row)

Pavel

(And would it be cool if Table C.1 [1] had some sort of javascript-y
filtering on reservedness categories, for just such kinds of bikeshedding?)

Regards,
-Chap

[1] https://www.postgresql.org/docs/13/sql-keywords-appendix.html#KEYWORDS-TABLE

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Vik Fearing

Date:

19 November 2020, 03:53:48

On 11/18/20 9:21 PM, Pavel Stehule wrote:
> postgres=# create or replace function bubu(a int, b int)
> returns void as $$
> #routine_label b
> begin
>   raise notice '% %', b.a, b.b;
> end;
> $$ language plpgsql;

Why not use the block labeling syntax we already have?

create or replace function bubu(a int, b int)
returns void as $$
<< b >>
begin
  raise notice '% %', b.a, b.b;
end;
$$ language plpgsql;

That doesn't currently work, but it could be made to.
-- 
Vik Fearing

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

19 November 2020, 07:32:30

this is less invasive, and probably more correct work with ns items patch.

čt 19. 11. 2020 v 1:54 odesílatel Vik Fearing <vik@postgresfriends.org> napsal:

On 11/18/20 9:21 PM, Pavel Stehule wrote:
> postgres=# create or replace function bubu(a int, b int)
> returns void as $$
> #routine_label b
> begin
> raise notice '% %', b.a, b.b;
> end;
> $$ language plpgsql;

Why not use the block labeling syntax we already have?

create or replace function bubu(a int, b int)
returns void as $$
<< b >>
begin
raise notice '% %', b.a, b.b;
end;
$$ language plpgsql;

That doesn't currently work, but it could be made to.

I don't think it is correct - in your example you are trying to merge two different namespaces - so minimally it should allow duplicate names in one namespace, or it breaks compatibility.

And I don't think so we want to lose information and separate access to function's arguments.

Regards

Pavel

--
Vik Fearing

Attachment

plpgsql-routine_label-option-v2.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

05 January 2021, 11:56:45

fix broken regress test

Regards

Pavel

Attachment

plpgsql-routine-label-option-v3.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Hannu Krosing

Date:

18 March 2021, 01:16:06

why are you using yet another special syntax for this ?

would it not be better to do something like this:
CREATE FUNCTION a_reall_long_and_winding_function_name(i int, out o int)
LANGUAGE plpgsql AS $plpgsql$
DECLARE
   args function_name_alias
BEGIN
  args.o = 2 * args.i
END;
$plpgsql$;

or at least do something based on block labels (see end of
https://www.postgresql.org/docs/13/plpgsql-structure.html )

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
...

maybe extend this to

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< functionlabel:args >>
<< outerblock >>
DECLARE
...

or replace 'functionlabel' with something less ugly :)

maybe <<< args >>>

Cheers
Hannu



On Wed, Mar 17, 2021 at 5:05 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> st 17. 3. 2021 v 9:20 odesílatel Michael Paquier <michael@paquier.xyz> napsal:
>>
>> On Wed, Mar 17, 2021 at 02:06:57PM +0800, Julien Rouhaud wrote:
>> > I also think that there should be a single usable top label, otherwise it will
>> > lead to confusing code and it can be a source of bug.
>>
>> Okay, that's fine by me.  Could it be possible to come up with an
>> approach that does not hijack the namespace list contruction and the
>> lookup logic as much as it does now?  I get it that the patch is done
>> this way because of the ordering of operations done for the initial ns
>> list creation and the grammar parsing that adds the routine label on
>> top of the root one, but I'd like to believe that there are more solid
>> ways to achieve that, like for example something that decouples the
>> root label and its alias, or something that associates an alias
>> directly with its PLpgSQL_nsitem entry?
>
>
> I am checking it now, and I don't see any easy solution. The namespace is a one direction tree - only backward
iterationfrom leafs to roots is supported. At the moment, when I try to replace the name of root ns_item, this root
ns_itemis referenced by the function's arguments (NS_TYPE_VAR type). So anytime I need some  helper ns_item node, that
canbe a persistent target for these nodes. In this case is a memory overhead of just one ns_item. 
>
> orig_label <- argument1 <- argument2
>
> The patch has to save the original orig_label because it can be referenced from argument1 or by "FOUND" variable. New
graphslooks like 
>
> new_label <- invalidated orig_label <- argument1 <- ...
>
> This tree has a different direction than is usual, and then replacing the root node is not simple.
>
> Second solution (significantly more simple) is an additional pointer in ns_item structure. In almost all cases this
pointerwill not be used. Because ns_item uses a flexible array, then union cannot be used. I implemented this in a
patchmarked as "alias-implementation". 
>
> What do you think about it?
>
> Pavel
>
>
>
>
>
>
>>
>> --
>> Michael

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Michael Paquier

Date:

18 March 2021, 01:32:47

On Wed, Mar 17, 2021 at 05:04:48PM +0100, Pavel Stehule wrote:
> This tree has a different direction than is usual, and then replacing the
> root node is not simple.

Yeah, it is not like we should redesign this whole part just for the
feature discussed here, and that may impact performance as the current
list handling is cheap now.

> Second solution (significantly more simple) is an additional pointer in
> ns_item structure. In almost all cases this pointer will not be used.
> Because ns_item uses a flexible array, then union cannot be used. I
> implemented this in a patch marked as "alias-implementation".
>
> What do you think about it?

I am not sure that it is necessary nor good to replace entirely the
root label.  So associating a new field directly into it sounds like a
promising approach?
--
Michael

Attachment

signature.asc

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 March 2021, 07:09:38

st 17. 3. 2021 v 23:32 odesílatel Michael Paquier <michael@paquier.xyz> napsal:

On Wed, Mar 17, 2021 at 05:04:48PM +0100, Pavel Stehule wrote:
> This tree has a different direction than is usual, and then replacing the
> root node is not simple.

Yeah, it is not like we should redesign this whole part just for the
feature discussed here, and that may impact performance as the current
list handling is cheap now.

> Second solution (significantly more simple) is an additional pointer in
> ns_item structure. In almost all cases this pointer will not be used.
> Because ns_item uses a flexible array, then union cannot be used. I
> implemented this in a patch marked as "alias-implementation".
>
> What do you think about it?

I am not sure that it is necessary nor good to replace entirely the
root label. So associating a new field directly into it sounds like a
promising approach?

I have not a problem with this.

Pavel

--
Michael

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 March 2021, 07:27:14

st 17. 3. 2021 v 23:16 odesílatel Hannu Krosing <hannuk@google.com> napsal:

why are you using yet another special syntax for this ?

would it not be better to do something like this:
CREATE FUNCTION a_reall_long_and_winding_function_name(i int, out o int)
LANGUAGE plpgsql AS $plpgsql$
DECLARE
args function_name_alias
BEGIN
args.o = 2 * args.i
END;
$plpgsql$;

or at least do something based on block labels (see end of
https://www.postgresql.org/docs/13/plpgsql-structure.html )

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
...

maybe extend this to

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< functionlabel:args >>
<< outerblock >>
DECLARE
...

or replace 'functionlabel' with something less ugly :)

maybe <<< args >>>

There are few main reasons:

a) compile options are available already - so I don't need invent new syntax - #OPTION DUMP, #PRINT_STRICT ON, #VARIABLE_CONFLICT ERROR

b) compile options are processed before any any other operations, so implementation can be very simple

c) implementation is very simple

I don't like an idea - <<prefix:value>> because you mix label syntax with some functionality, and <<x>> and <<<x>>> are visually similar

"#routine_label xxx" is not maybe visually nice (like other compile options in plpgsql), but has good readability, simplicity, verbosity

Because we already have this functionality (compile options), and because this functionality is working well (there are not any limits from this syntax), I strongly prefer to use it. We should not invite new syntax when some already available syntax can work well. We can talk about the keyword ROUTINE_LABEL - maybe #OPTION ROUTINE_LABEL xxx can look better, but I think so using the compile option is a good solution.

Regards

Pavel

Cheers
Hannu

On Wed, Mar 17, 2021 at 5:05 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> st 17. 3. 2021 v 9:20 odesílatel Michael Paquier <michael@paquier.xyz> napsal:
>>
>> On Wed, Mar 17, 2021 at 02:06:57PM +0800, Julien Rouhaud wrote:
>> > I also think that there should be a single usable top label, otherwise it will
>> > lead to confusing code and it can be a source of bug.
>>
>> Okay, that's fine by me. Could it be possible to come up with an
>> approach that does not hijack the namespace list contruction and the
>> lookup logic as much as it does now? I get it that the patch is done
>> this way because of the ordering of operations done for the initial ns
>> list creation and the grammar parsing that adds the routine label on
>> top of the root one, but I'd like to believe that there are more solid
>> ways to achieve that, like for example something that decouples the
>> root label and its alias, or something that associates an alias
>> directly with its PLpgSQL_nsitem entry?
>
>
> I am checking it now, and I don't see any easy solution. The namespace is a one direction tree - only backward iteration from leafs to roots is supported. At the moment, when I try to replace the name of root ns_item, this root ns_item is referenced by the function's arguments (NS_TYPE_VAR type). So anytime I need some helper ns_item node, that can be a persistent target for these nodes. In this case is a memory overhead of just one ns_item.
>
> orig_label <- argument1 <- argument2
>
> The patch has to save the original orig_label because it can be referenced from argument1 or by "FOUND" variable. New graphs looks like
>
> new_label <- invalidated orig_label <- argument1 <- ...
>
> This tree has a different direction than is usual, and then replacing the root node is not simple.
>
> Second solution (significantly more simple) is an additional pointer in ns_item structure. In almost all cases this pointer will not be used. Because ns_item uses a flexible array, then union cannot be used. I implemented this in a patch marked as "alias-implementation".
>
> What do you think about it?
>
> Pavel
>
>
>
>
>
>
>>
>> --
>> Michael

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Hannu Krosing

Date:

18 March 2021, 17:19:12

On Thu, Mar 18, 2021 at 5:27 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> There are few main reasons:
>
> a) compile options are available already - so I don't need invent new syntax - #OPTION DUMP, #PRINT_STRICT ON,
#VARIABLE_CONFLICTERROR
 

Are these documented anywhere ?

At least a quick search for pl/pgsql + #OPTION DUMP, #PRINT_STRICT ON,
#VARIABLE_CONFLICT ERROR returned nothing relevant and also searching
in

If pl/pgsql actually supports any of these, these should get documented!



For me the most logical place for declaring a function name alias
would be to allow  ALIAS FOR the function name in DECLARE section.

plpgsql_test=# CREATE FUNCTION
a_function_with_an_inconveniently_long_name(i int , OUT o int)
LANGUAGE plpgsql AS $plpgsql$
DECLARE
   fnarg ALIAS FOR a_function_with_an_inconveniently_long_name;
BEGIN
   fnarg.o = 2 * fnarg.i;
END;
$plpgsql$;

but unfortunately this currently returns

ERROR:  42704: variable "a_function_with_an_inconveniently_long_name"
does not exist
LINE 4:    fnarg ALIAS FOR a_function_with_an_inconveniently_long_na...
                           ^
LOCATION:  plpgsql_yyparse, pl_gram.y:677

Variation could be

DECLARE
   fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;

so it is clear that we are aliasing current function name

or even make the function name optional, as there can only be one, so

DECLARE
   fnarg ALIAS FOR FUNCTION;


Cheers
Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 March 2021, 17:45:16

čt 18. 3. 2021 v 15:19 odesílatel Hannu Krosing <hannuk@google.com> napsal:

On Thu, Mar 18, 2021 at 5:27 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> There are few main reasons:
>
> a) compile options are available already - so I don't need invent new syntax - #OPTION DUMP, #PRINT_STRICT ON, #VARIABLE_CONFLICT ERROR

Are these documented anywhere ?

At least a quick search for pl/pgsql + #OPTION DUMP, #PRINT_STRICT ON,
#VARIABLE_CONFLICT ERROR returned nothing relevant and also searching
in

If pl/pgsql actually supports any of these, these should get documented!

it is documented, please look to following links (but #option dump is undocumented, others are documented)

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

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

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN    SELECT users.userid INTO STRICT userid        FROM users WHERE users.username = get_userid.username;    RETURN userid;
END;
$$ LANGUAGE plpgsql;

For me the most logical place for declaring a function name alias
would be to allow ALIAS FOR the function name in DECLARE section.

plpgsql_test=# CREATE FUNCTION
a_function_with_an_inconveniently_long_name(i int , OUT o int)
LANGUAGE plpgsql AS $plpgsql$
DECLARE
fnarg ALIAS FOR a_function_with_an_inconveniently_long_name;
BEGIN
fnarg.o = 2 * fnarg.i;
END;
$plpgsql$;

but unfortunately this currently returns

ERROR: 42704: variable "a_function_with_an_inconveniently_long_name"
does not exist
LINE 4: fnarg ALIAS FOR a_function_with_an_inconveniently_long_na...
^
LOCATION: plpgsql_yyparse, pl_gram.y:677

Variation could be

DECLARE
fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;

so it is clear that we are aliasing current function name

or even make the function name optional, as there can only be one, so

DECLARE
fnarg ALIAS FOR FUNCTION;

Why you think so it is better than just

#routine_label fnarg

Maybe the name of the option can be routine_alias? Is it better for you?

My objection to DECLARE is freedom of this clause. It can be used everywhere. the effect of DECLARE is block limited. So theoretically somebody can write

BEGIN

DECLARE fnarg ALIAS FOR FUNCTION;

BEGIN

END;

It disallows simple implementation.

Regards

Pavel

Cheers
Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Hannu Krosing

Date:

18 March 2021, 17:48:55

I went to archives and read the whole discussion for this from the beginning

I did not really understand, why using the ALIAS FOR syntax is significantly
harder to implement then #routine_label

The things you mentioned as currently using #OPTION seem to be in a different
category from the aliases and block labels - they are more like pragmas -  so to
me it still feels inconsistent to use #routine_label for renaming the
outer namespace.


Also checked code and indeed there is support for #OPTION DUMP and
#VARIABLE_CONFLICT ERROR
Is it documented and just hard to find ?


On Thu, Mar 18, 2021 at 3:19 PM Hannu Krosing <hannuk@google.com> wrote:
>
> On Thu, Mar 18, 2021 at 5:27 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >
> >
> > There are few main reasons:
> >
> > a) compile options are available already - so I don't need invent new syntax - #OPTION DUMP, #PRINT_STRICT ON,
#VARIABLE_CONFLICTERROR
 
>
> Are these documented anywhere ?
>
> At least a quick search for pl/pgsql + #OPTION DUMP, #PRINT_STRICT ON,
> #VARIABLE_CONFLICT ERROR returned nothing relevant and also searching
> in
>
> If pl/pgsql actually supports any of these, these should get documented!
>
>
>
> For me the most logical place for declaring a function name alias
> would be to allow  ALIAS FOR the function name in DECLARE section.
>
> plpgsql_test=# CREATE FUNCTION
> a_function_with_an_inconveniently_long_name(i int , OUT o int)
> LANGUAGE plpgsql AS $plpgsql$
> DECLARE
>    fnarg ALIAS FOR a_function_with_an_inconveniently_long_name;
> BEGIN
>    fnarg.o = 2 * fnarg.i;
> END;
> $plpgsql$;
>
> but unfortunately this currently returns
>
> ERROR:  42704: variable "a_function_with_an_inconveniently_long_name"
> does not exist
> LINE 4:    fnarg ALIAS FOR a_function_with_an_inconveniently_long_na...
>                            ^
> LOCATION:  plpgsql_yyparse, pl_gram.y:677
>
> Variation could be
>
> DECLARE
>    fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;
>
> so it is clear that we are aliasing current function name
>
> or even make the function name optional, as there can only be one, so
>
> DECLARE
>    fnarg ALIAS FOR FUNCTION;
>
>
> Cheers
> Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Hannu Krosing

Date:

18 March 2021, 17:59:33

On Thu, Mar 18, 2021 at 3:45 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> čt 18. 3. 2021 v 15:19 odesílatel Hannu Krosing <hannuk@google.com> napsal:
...
>> Variation could be
>>
>> DECLARE
>>    fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;
>>
>> so it is clear that we are aliasing current function name
>>
>>
>> or even make the function name optional, as there can only be one, so
>>
>> DECLARE
>>    fnarg ALIAS FOR FUNCTION;
>
>
> Why you think so it is better than just
>
> #routine_label fnarg
>
> ?

It just adds already a *third* way to (re)name things, in addition to
<< blocklabel >>
and ALIAS FOR

For some reason it feels to me similar to having completely different
syntax rules
for function names, argument names and variable names instead of all having to
follow rules for "identifiers"

For cleanness/orthogonality I would also prefer the blocklables to be in DECLARE
for each block, but this train has already left :)
Though we probably could add alternative syntax ALIAS FOR BLOCK ?

> Maybe the name of the option can be routine_alias? Is it better for you?

I dont think the name itself is bad, as it is supposed to be used for
both FUNCTION
and PROCEDURE renaming

> My objection to DECLARE is freedom of this clause. It can be used everywhere.
> the effect of DECLARE is block limited. So theoretically somebody can write
>
> BEGIN
>   ..
>   DECLARE fnarg ALIAS FOR FUNCTION;
>   BEGIN
>     ..
>   END;
>
> END;
>
> It disallows simple implementation.

We could limit ALIAS FOR FUNCTION to outermost block only, and revisit
this later
if there are loud complaints (which I don't think there will be :) )



Cheers
Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 March 2021, 18:00:24

čt 18. 3. 2021 v 15:49 odesílatel Hannu Krosing <hannuk@google.com> napsal:

I went to archives and read the whole discussion for this from the beginning

I did not really understand, why using the ALIAS FOR syntax is significantly
harder to implement then #routine_label

just because it can be used in different places - for example in nested blocks. And then you need to create really new aliases for these object in current namespace. And it is not easy because we cannot to iterate in ns_items tree from root

The things you mentioned as currently using #OPTION seem to be in a different
category from the aliases and block labels - they are more like pragmas - so to
me it still feels inconsistent to use #routine_label for renaming the
outer namespace.

I think this feature allows for more concepts.

Also checked code and indeed there is support for #OPTION DUMP and
#VARIABLE_CONFLICT ERROR
Is it documented and just hard to find ?

please, see my previous mail. There was links

On Thu, Mar 18, 2021 at 3:19 PM Hannu Krosing <hannuk@google.com> wrote:
>
> On Thu, Mar 18, 2021 at 5:27 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >
> >
> > There are few main reasons:
> >
> > a) compile options are available already - so I don't need invent new syntax - #OPTION DUMP, #PRINT_STRICT ON, #VARIABLE_CONFLICT ERROR
>
> Are these documented anywhere ?
>
> At least a quick search for pl/pgsql + #OPTION DUMP, #PRINT_STRICT ON,
> #VARIABLE_CONFLICT ERROR returned nothing relevant and also searching
> in
>
> If pl/pgsql actually supports any of these, these should get documented!
>
>
>
> For me the most logical place for declaring a function name alias
> would be to allow ALIAS FOR the function name in DECLARE section.
>
> plpgsql_test=# CREATE FUNCTION
> a_function_with_an_inconveniently_long_name(i int , OUT o int)
> LANGUAGE plpgsql AS $plpgsql$
> DECLARE
> fnarg ALIAS FOR a_function_with_an_inconveniently_long_name;
> BEGIN
> fnarg.o = 2 * fnarg.i;
> END;
> $plpgsql$;
>
> but unfortunately this currently returns
>
> ERROR: 42704: variable "a_function_with_an_inconveniently_long_name"
> does not exist
> LINE 4: fnarg ALIAS FOR a_function_with_an_inconveniently_long_na...
> ^
> LOCATION: plpgsql_yyparse, pl_gram.y:677
>
> Variation could be
>
> DECLARE
> fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;
>
> so it is clear that we are aliasing current function name
>
> or even make the function name optional, as there can only be one, so
>
> DECLARE
> fnarg ALIAS FOR FUNCTION;
>
>
> Cheers
> Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

18 March 2021, 18:22:25

čt 18. 3. 2021 v 15:59 odesílatel Hannu Krosing <hannuk@google.com> napsal:

On Thu, Mar 18, 2021 at 3:45 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> čt 18. 3. 2021 v 15:19 odesílatel Hannu Krosing <hannuk@google.com> napsal:
...
>> Variation could be
>>
>> DECLARE
>> fnarg ALIAS FOR FUNCTION a_function_with_an_inconveniently_long_name;
>>
>> so it is clear that we are aliasing current function name
>>
>>
>> or even make the function name optional, as there can only be one, so
>>
>> DECLARE
>> fnarg ALIAS FOR FUNCTION;
>
>
> Why you think so it is better than just
>
> #routine_label fnarg
>
> ?

It just adds already a *third* way to (re)name things, in addition to
<< blocklabel >>
and ALIAS FOR

For some reason it feels to me similar to having completely different
syntax rules
for function names, argument names and variable names instead of all having to
follow rules for "identifiers"

For cleanness/orthogonality I would also prefer the blocklables to be in DECLARE
for each block, but this train has already left :)
Though we probably could add alternative syntax ALIAS FOR BLOCK ?

why? Is it a joke?

you are defining a block label, and you want to in the same block redefine some outer label? I don't think it is a good idea.

> Maybe the name of the option can be routine_alias? Is it better for you?

I dont think the name itself is bad, as it is supposed to be used for
both FUNCTION
and PROCEDURE renaming

> My objection to DECLARE is freedom of this clause. It can be used everywhere.
> the effect of DECLARE is block limited. So theoretically somebody can write
>
> BEGIN
> ..
> DECLARE fnarg ALIAS FOR FUNCTION;
> BEGIN
> ..
> END;
>
> END;
>
> It disallows simple implementation.

We could limit ALIAS FOR FUNCTION to outermost block only, and revisit
this later
if there are loud complaints (which I don't think there will be :) )

Inside the parser you don't know so you are in the outermost block only.

I play with it and I see two objections against DECLARE syntax

1. the scope is in block. So you cannot use aliases for default arguments (or maybe yes, but is it correct?)

CREATE OR REPLACE FUNCTION fx(a int, b int)

RETURNS ... AS $$

DECLARE

x1 int := fx.a;

fnarg AS ALIAS FOR FUNCTION;

x2 int := fnarg.a; -- should be this alias active now?

BEGIN

2. "ALIAS FOR FUNCTION" is not well named. In some languages, and in ADA language too. You can rename an external function just for current scope. You can find some examples like

DECLARE

s ALIAS FOR FUNCTION sin;

BEGIN

s(1); -- returns result of function sin

other example - recursive function

CREATE OR REPLACE FUNCTION some_very_long()

RETURNS int AS $$

DECLARE

thisfx ALIAS FOR FUNCTION;

BEGIN

var := thisfx(...);

But we don't support this feature. We are changing just a top scope's label. So syntax "ALIAS FOR FUNCTION is not good. The user can have false hopes

Cheers
Hannu

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Hannu Krosing

Date:

19 March 2021, 16:14:24

On Thu, Mar 18, 2021 at 4:23 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

> But we don't support this feature. We are changing just a top scope's label. So syntax "ALIAS FOR FUNCTION is not
good.The user can have false hopes
 

In this case it looks like it should go together with other labels and
have << label_here >> syntax ?

And we are back to the question of where to put this top scope label :)

Maybe we cloud still pull in the function arguments into the outermost
blocks scope, and advise users to have an extra block if they want to
have same names in both ?


CREATE OR REPLACE FUNCTION fx(a int, b int)
RETURNS ... AS $$
<< fnargs >>
BEGIN
  << topblock >>
  DECLARE
    a int := fnargs.a * 2;
    b int := topblock.a + 2;
  BEGIN
    ....
  END;
END;
$$;

and we could make the empty outer block optional and treat two
consecutive labels as top scope label and outermost block label

CREATE OR REPLACE FUNCTION fx(a int, b int)
RETURNS ... AS $$
<< fnargs >>
<< topblock >>
  DECLARE
    a int := fnargs.a * 2;
    b int := topblock.a + 2;
  BEGIN
    ....
END;
$$;

But I agree that this is also not ideal.


And

CREATE OR REPLACE FUNCTION fx(a int, b int)
WITH (TOPSCOPE_LABEL fnargs)
RETURNS ... AS $$
<< topblock >>
  DECLARE
    a int := fnargs.a * 2;
    b int := topblock.a + 2;
  BEGIN
    ....
END;
$$;

Is even more inconsistent than #option syntax


Cheers
Hannu

PS:

>
> For cleanness/orthogonality I would also prefer the blocklables to be in DECLARE
> for each block, but this train has already left :)
> Though we probably could add alternative syntax ALIAS FOR BLOCK ?

>
> why? Is it a joke?
>
> you are defining a block label, and you want to in the same block redefine some outer label? I don't think it is a
goodidea.

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

21 March 2021, 18:33:41

pá 19. 3. 2021 v 14:14 odesílatel Hannu Krosing <hannuk@google.com> napsal:

On Thu, Mar 18, 2021 at 4:23 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

> But we don't support this feature. We are changing just a top scope's label. So syntax "ALIAS FOR FUNCTION is not good. The user can have false hopes

In this case it looks like it should go together with other labels and
have << label_here >> syntax ?

And we are back to the question of where to put this top scope label :)

Maybe we cloud still pull in the function arguments into the outermost
blocks scope, and advise users to have an extra block if they want to
have same names in both ?

CREATE OR REPLACE FUNCTION fx(a int, b int)
RETURNS ... AS $$
<< fnargs >>
BEGIN
<< topblock >>
DECLARE
a int := fnargs.a * 2;
b int := topblock.a + 2;
BEGIN
....
END;
END;
$$;

It looks pretty messy.

and we could make the empty outer block optional and treat two
consecutive labels as top scope label and outermost block label

CREATE OR REPLACE FUNCTION fx(a int, b int)
RETURNS ... AS $$
<< fnargs >>
<< topblock >>
DECLARE
a int := fnargs.a * 2;
b int := topblock.a + 2;
BEGIN
....
END;
$$;

But I agree that this is also not ideal.

I agree with you :). The problem is in semantics - top outer namespace is external - is not visible, and so placing some label anywhere in code should to looks scary

And

CREATE OR REPLACE FUNCTION fx(a int, b int)
WITH (TOPSCOPE_LABEL fnargs)
RETURNS ... AS $$
<< topblock >>
DECLARE
a int := fnargs.a * 2;
b int := topblock.a + 2;
BEGIN
....
END;
$$;

Is even more inconsistent than #option syntax

yes. This syntax has sense. But this syntax should be implemented from zero (although it will be only a few lines, and then it is not a big issue).

Bigger issue is a risk of confusion with the "WITH ()" clause of CREATE TABLE, because syntax is exactly the same, but it holds a list of GUC settings. And it does exactly what compile options does.

CREATE TABLE foo(...) WITH (autovacuum off)

Please try to compare:

CREATE OR REPLACE FUNCTION fx(a int, b int)
WITH (TOPSCOPE_LABEL fnargs)
RETURNS ... AS $$
<< topblock >>
DECLARE
a int := fnargs.a * 2;
b int := topblock.a + 2;

CREATE OR REPLACE FUNCTION fx(a int, b int)
RETURNS ... AS $$

#TOPSCOPE_LABEL fnargs

<< topblock >>
DECLARE
a int := fnargs.a * 2;
b int := topblock.a + 2;

I don't see too big a difference, and with the compile option, I don't need to introduce new possibly confusing syntax. If we want to implement your proposed syntax, then we should support all plpgsql compile options there too (for consistency). The syntax that you propose has logic. But I am afraid about possible confusion with the same clause with different semantics of other DDL commands, and then I am not sure if cost is adequate to benefit.

Regards

Pavel

Cheers
Hannu

PS:

>
> For cleanness/orthogonality I would also prefer the blocklables to be in DECLARE
> for each block, but this train has already left :)
> Though we probably could add alternative syntax ALIAS FOR BLOCK ?

>
> why? Is it a joke?
>
> you are defining a block label, and you want to in the same block redefine some outer label? I don't think it is a good idea.

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

24 March 2021, 10:09:13

st 17. 3. 2021 v 23:32 odesílatel Michael Paquier <michael@paquier.xyz> napsal:

On Wed, Mar 17, 2021 at 05:04:48PM +0100, Pavel Stehule wrote:
> This tree has a different direction than is usual, and then replacing the
> root node is not simple.

Yeah, it is not like we should redesign this whole part just for the
feature discussed here, and that may impact performance as the current
list handling is cheap now.

> Second solution (significantly more simple) is an additional pointer in
> ns_item structure. In almost all cases this pointer will not be used.
> Because ns_item uses a flexible array, then union cannot be used. I
> implemented this in a patch marked as "alias-implementation".
>
> What do you think about it?

I am not sure that it is necessary nor good to replace entirely the
root label. So associating a new field directly into it sounds like a
promising approach?

Here is rebased patch

I am continuing to talk with Hannu about syntax. I think so using the compile option is a good direction, but maybe renaming from "routine_label" to "topscope_label" (proposed by Hannu) or maybe "outerscope_label" can be a good idea.

Regards

Pavel

--
Michael

Attachment

plpgsql-routine-label-20210324.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Erik Rijkers

Date:

24 March 2021, 10:42:30

> On 2021.03.24. 08:09 Pavel Stehule <pavel.stehule@gmail.com> wrote:
> [...]
> [plpgsql-routine-label-20210324.patch]


Hi,

In that sgml

"the functions' arguments"

should be

"the function's arguments"


Erik

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

24 March 2021, 21:44:33

st 24. 3. 2021 v 8:42 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

> On 2021.03.24. 08:09 Pavel Stehule <pavel.stehule@gmail.com> wrote:
> [...]
> [plpgsql-routine-label-20210324.patch]

Hi,

In that sgml

"the functions' arguments"

should be

"the function's arguments"

fixed,

thank you for check

Erik

Attachment

plpgsql-routine-label-20210324-2.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

28 March 2021, 14:48:35

I really like this feature.

Is there a way to avoid having to write the "#ROUTINE_LABEL" for every function defined?

I'm thinking if a company writing a lot of plpgsql functions want to decide on a company wide hard-coded label to use for all their plpgsql functions. Could it be set globally and enforced for the entire code base somehow?

It would of course be a problem since other plpgsql extensions could be in conflict with such a label,

so maybe we could allow creating a new language, "plmycorp", using the same handlers as plpgsql,

with the addition of a hard-coded #ROUTINE_LABEL as a part of the language definition,

which would then be forbidden to override in function definitions.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

28 March 2021, 15:27:51

ne 28. 3. 2021 v 13:49 odesílatel Joel Jacobson <joel@compiler.org> napsal:

+1

I really like this feature.

Is there a way to avoid having to write the "#ROUTINE_LABEL" for every function defined?

I'm thinking if a company writing a lot of plpgsql functions want to decide on a company wide hard-coded label to use for all their plpgsql functions. Could it be set globally and enforced for the entire code base somehow?

It would of course be a problem since other plpgsql extensions could be in conflict with such a label,
so maybe we could allow creating a new language, "plmycorp", using the same handlers as plpgsql,
with the addition of a hard-coded #ROUTINE_LABEL as a part of the language definition,
which would then be forbidden to override in function definitions.

There is no technical problem, but if I remember well, the committers don't like configuration settings that globally modify the behaviour. There can be a lot of issues related to recovery from backups or porting to others systems. Personally I understand and I accept it. There should be some press for ensuring some level of compatibility. And can be really unfanny if you cannot read routine from backups because you have "bad" postgres configuration. It can work in your company, but this feature can be used by others, and they can have problems.

On second hand - it is very easy to write a tool that checks wanted #ROUTINE_LABEL in every routine.

Regards

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

28 March 2021, 15:44:51

On Sun, Mar 28, 2021, at 14:27, Pavel Stehule wrote:

ne 28. 3. 2021 v 13:49 odesílatel Joel Jacobson <joel@compiler.org> napsal:
It would of course be a problem since other plpgsql extensions could be in conflict with such a label,
so maybe we could allow creating a new language, "plmycorp", using the same handlers as plpgsql,
with the addition of a hard-coded #ROUTINE_LABEL as a part of the language definition,
which would then be forbidden to override in function definitions.

There is no technical problem, but if I remember well, the committers don't like configuration settings that globally modify the behaviour. There can be a lot of issues related to recovery from backups or porting to others systems. Personally I understand and I accept it. There should be some press for ensuring some level of compatibility. And can be really unfanny if you cannot read routine from backups because you have "bad" postgres configuration. It can work in your company, but this feature can be used by others, and they can have problems.

I also dislike configuration that modify behaviour, but I don't think that's what I proposed. Creating a new language would not be configuration, it would be part of the dumpable/restorable schema, just like the functions.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

28 March 2021, 16:12:39

ne 28. 3. 2021 v 14:45 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Sun, Mar 28, 2021, at 14:27, Pavel Stehule wrote:
ne 28. 3. 2021 v 13:49 odesílatel Joel Jacobson <joel@compiler.org> napsal:
It would of course be a problem since other plpgsql extensions could be in conflict with such a label,
so maybe we could allow creating a new language, "plmycorp", using the same handlers as plpgsql,
with the addition of a hard-coded #ROUTINE_LABEL as a part of the language definition,
which would then be forbidden to override in function definitions.

There is no technical problem, but if I remember well, the committers don't like configuration settings that globally modify the behaviour. There can be a lot of issues related to recovery from backups or porting to others systems. Personally I understand and I accept it. There should be some press for ensuring some level of compatibility. And can be really unfanny if you cannot read routine from backups because you have "bad" postgres configuration. It can work in your company, but this feature can be used by others, and they can have problems.

I also dislike configuration that modify behaviour, but I don't think that's what I proposed. Creating a new language would not be configuration, it would be part of the dumpable/restorable schema, just like the functions.

Maybe I don't understand your proposal well, I am sorry. Creating your own language should not be hard, but in this case the plpgsql is not well extendable. The important structures are private. You can do it, but you should do a full copy of plpgsql. I don't think so you can reuse handler's routines - it is not prepared for it. Unfortunately, the handler expects only function oid and arguments, and there is not a possibility how to pass any options (if I know).

Regards

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

28 March 2021, 16:52:35

On Sun, Mar 28, 2021, at 15:12, Pavel Stehule wrote:

Maybe I don't understand your proposal well, I am sorry. Creating your own language should not be hard, but in this case the plpgsql is not well extendable. The important structures are private. You can do it, but you should do a full copy of plpgsql. I don't think so you can reuse handler's routines - it is not prepared for it. Unfortunately, the handler expects only function oid and arguments, and there is not a possibility how to pass any options (if I know).

Sorry, let me clarify what I mean.

I mean something along the lines of adding a new nullable column to "pg_language", maybe "lanroutinelabel"?

All other columns (lanispl, lanpltrusted, lanplcallfoid, laninline, lanvalidator) would reuse the same values as plpgsql.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

28 March 2021, 17:04:37

On Sun, Mar 28, 2021 at 03:52:35PM +0200, Joel Jacobson wrote:
> On Sun, Mar 28, 2021, at 15:12, Pavel Stehule wrote:
> > Maybe I don't understand your proposal well, I am sorry. Creating your own language should not be hard, but in this
casethe plpgsql is not well extendable. The important structures are private. You can do it, but you should do a full
copyof plpgsql. I don't think so you can reuse handler's routines - it is not prepared for it. Unfortunately, the
handlerexpects only function oid and arguments, and there is not a possibility how to pass any options (if I know). 

> 
> Sorry, let me clarify what I mean.
> 
> I mean something along the lines of adding a new nullable column to "pg_language", maybe "lanroutinelabel"?
> All other columns (lanispl, lanpltrusted, lanplcallfoid, laninline, lanvalidator) would reuse the same values as
plpgsql.

It doesn't seem like a good way to handle some customization of existing
language.  It's too specialized and soon people will ask for fields to change
the default behavior of many other things and a default "routine label" may not
make sense in all languages.  If we were to do that we should probably add a
new function that would be called to setup all language specific stuff that
users may want to change.

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

28 March 2021, 18:12:10

ne 28. 3. 2021 v 16:04 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

On Sun, Mar 28, 2021 at 03:52:35PM +0200, Joel Jacobson wrote:
> On Sun, Mar 28, 2021, at 15:12, Pavel Stehule wrote:
> > Maybe I don't understand your proposal well, I am sorry. Creating your own language should not be hard, but in this case the plpgsql is not well extendable. The important structures are private. You can do it, but you should do a full copy of plpgsql. I don't think so you can reuse handler's routines - it is not prepared for it. Unfortunately, the handler expects only function oid and arguments, and there is not a possibility how to pass any options (if I know).
>
> Sorry, let me clarify what I mean.
>
> I mean something along the lines of adding a new nullable column to "pg_language", maybe "lanroutinelabel"?
> All other columns (lanispl, lanpltrusted, lanplcallfoid, laninline, lanvalidator) would reuse the same values as plpgsql.

It doesn't seem like a good way to handle some customization of existing
language. It's too specialized and soon people will ask for fields to change
the default behavior of many other things and a default "routine label" may not
make sense in all languages. If we were to do that we should probably add a
new function that would be called to setup all language specific stuff that
users may want to change.

Yes, this has a benefit just for plpgsql. I can imagine a little bit different API of plpgsql that allows more direct calls than now. For example

static PLpgSQL_function *
do_compile(FunctionCallInfo fcinfo,
<--><--> HeapTuple procTup,
<--><--> PLpgSQL_function *function,
<--><--> PLpgSQL_func_hashkey *hashkey,
<--><--> bool forValidator)
{

if the function do_compile will be public, and if there will be new argument - compile_options, then can easily force these options, and is relatively easy to reuse plpgsql as base for own language.

It can be interesting for plpgsql_check too. But I am not sure if it is too strong an argument for Tom :)

Regards

Pavel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

28 March 2021, 18:58:51

On Sun, Mar 28, 2021 at 05:12:10PM +0200, Pavel Stehule wrote:
> 
> Yes, this has a benefit just for plpgsql. I can imagine a little bit
> different API of plpgsql that allows more direct calls than now. For example
> 
> static PLpgSQL_function *
> do_compile(FunctionCallInfo fcinfo,
> <--><-->   HeapTuple procTup,
> <--><-->   PLpgSQL_function *function,
> <--><-->   PLpgSQL_func_hashkey *hashkey,
> <--><-->   bool forValidator)
> {
> 
> if the function do_compile will be public, and if there will be new
> argument - compile_options, then can easily force these options, and is
> relatively easy to reuse plpgsql as base for own language.
> 
> It can be interesting for plpgsql_check too. But I am not sure if it is too
> strong an argument for Tom :)

I think that it would be sensible to do something along those lines.
Especially if it allows better static analysis for tools like plpgsql_check.
But that's definitely out of this patch's scope, and not pg14 material.

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

28 March 2021, 19:00:55

ne 28. 3. 2021 v 17:58 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

On Sun, Mar 28, 2021 at 05:12:10PM +0200, Pavel Stehule wrote:
>
> Yes, this has a benefit just for plpgsql. I can imagine a little bit
> different API of plpgsql that allows more direct calls than now. For example
>
> static PLpgSQL_function *
> do_compile(FunctionCallInfo fcinfo,
> <--><--> HeapTuple procTup,
> <--><--> PLpgSQL_function *function,
> <--><--> PLpgSQL_func_hashkey *hashkey,
> <--><--> bool forValidator)
> {
>
> if the function do_compile will be public, and if there will be new
> argument - compile_options, then can easily force these options, and is
> relatively easy to reuse plpgsql as base for own language.
>
> It can be interesting for plpgsql_check too. But I am not sure if it is too
> strong an argument for Tom :)

I think that it would be sensible to do something along those lines.
Especially if it allows better static analysis for tools like plpgsql_check.
But that's definitely out of this patch's scope, and not pg14 material.

yes, sure.

Pavel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

06 January 2022, 10:02:39

Hi Pavel,

On Wed, Mar 24, 2021 at 08:09:13AM +0100, Pavel Stehule wrote:
> 
> I am continuing to  talk with Hannu about syntax. I think so using the
> compile option is a good direction, but maybe renaming from "routine_label"
> to "topscope_label" (proposed by Hannu) or maybe "outerscope_label" can be
> a good idea.

It's not clear to me what is the status of this patch.  You sent some updated
patch since with some typo fixes but I don't see any conclusion about the
direction this patch should go and/or how to name the new option.

Should the patch be reviewed or switched to Waiting on Author?

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 10:52:23

čt 6. 1. 2022 v 8:02 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

Hi Pavel,

On Wed, Mar 24, 2021 at 08:09:13AM +0100, Pavel Stehule wrote:
>
> I am continuing to talk with Hannu about syntax. I think so using the
> compile option is a good direction, but maybe renaming from "routine_label"
> to "topscope_label" (proposed by Hannu) or maybe "outerscope_label" can be
> a good idea.

It's not clear to me what is the status of this patch. You sent some updated
patch since with some typo fixes but I don't see any conclusion about the
direction this patch should go and/or how to name the new option.

Should the patch be reviewed or switched to Waiting on Author?

I am not sure if there is enough agreement and if there is enough necessity for this feature.

In this discussion there were more general questions about future development of plpgsql (about possible configurations and compiler parametrizations, and how to switch the configuration on global and on local levels). This is not fully solved yet. This is probably the reason why discussion about this specific feature (and very simple feature) has not reached a clear end. Generally, this is the same problem in Postgres.

Personally, I don't want to write a complicated patch for this relatively trivial (and not too important feature). Sure, then it cannot solve more general questions. I think the plpgsql option can work well - maybe with the keyword "top_label" or "root_label". Probably some enhancing of the DECLARE statement can work too (if it will be done well). But it requires more work, because the DECLARE statement has block scope, and then the alias of the top label has to have block scope too. Theoretically we can propagate it to GUC too - same like plpgsql.variable_conflict. This simple design can work (my opinion), but I understand so it is not a general reply for people who can have more control over the behavior of plpgsql. But I think it is a different issue and should be solved separately (and this pretty complex problem, because Postgres together with PLpgSQL mix different access models - access rights, searching path, local scope, and we miss possible parametrization on schema level what has most sense for PLpgSQL).

Regards

Pavel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

06 January 2022, 12:05:32

On Thu, Jan 06, 2022 at 08:52:23AM +0100, Pavel Stehule wrote:
> 
> I am not sure if there is enough agreement and if there is enough necessity
> for this feature.
> 
> In this discussion there were more general questions about future
> development of plpgsql (about possible configurations and compiler
> parametrizations, and how to switch the configuration on global and on
> local levels). This is not fully solved yet. This is probably the reason
> why discussion about this specific feature (and very simple feature) has
> not reached a clear end. Generally, this is the same problem in Postgres.

I agree, but on the other hand I don't see how defining a top level block
alias identical for every single plpgsql function would really make sense.
Not every function has a very long name and would benefit from it, and no one
can really assume that e.g. "root" or whatever configured name won't be used in
any plpgsql function on that database or cluster.  So while having some global
configuration infrastructure can be useful I still don't think that it could be
used for this purpose.

Anyway, the only committer that showed some interest in the feature is Michael,
and he seemed ok in principle with the "alias-implementation" approach.
Michael, did you have a look at this version ([1]), or do you think it should
simply be rejected?

[1]: https://www.postgresql.org/message-id/attachment/120789/plpgsql-routine-label-option-alias-implementation.patch

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 16:28:10

On Thu, Jan 6, 2022, at 10:05, Julien Rouhaud wrote:

> I agree, but on the other hand I don't see how defining a top level block

> alias identical for every single plpgsql function would really make sense.

> Not every function has a very long name and would benefit from it, and no one

> can really assume that e.g. "root" or whatever configured name won't be used in

> any plpgsql function on that database or cluster. So while having some global

> configuration infrastructure can be useful I still don't think that it could be

> used for this purpose.

How about using the existing reserved keyword "in" followed by "." (dot) and then the function parameter name?

This idea is based on the assumption "in." would always be a syntax error everywhere in both SQL and PL/pgSQL,

so if we would introduce such a syntax, no existing code could be affected, except currently invalid code.

I wouldn't mind using "in." to refer to IN/OUT/INOUT parameters and not only IN ones, it's a minor confusion that could be explained in the docs.

Also, "out." wouldn't work, since "out" is not a reserved keyword.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 17:05:12

čt 6. 1. 2022 v 14:28 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 10:05, Julien Rouhaud wrote:
> I agree, but on the other hand I don't see how defining a top level block
> alias identical for every single plpgsql function would really make sense.
> Not every function has a very long name and would benefit from it, and no one
> can really assume that e.g. "root" or whatever configured name won't be used in
> any plpgsql function on that database or cluster. So while having some global
> configuration infrastructure can be useful I still don't think that it could be
> used for this purpose.

How about using the existing reserved keyword "in" followed by "." (dot) and then the function parameter name?

This idea is based on the assumption "in." would always be a syntax error everywhere in both SQL and PL/pgSQL,
so if we would introduce such a syntax, no existing code could be affected, except currently invalid code.

I wouldn't mind using "in." to refer to IN/OUT/INOUT parameters and not only IN ones, it's a minor confusion that could be explained in the docs.

You are right, in.outvar looks messy. Moreover, maybe the SQL parser can have a problem with it.

Regards

Pavel

Also, "out." wouldn't work, since "out" is not a reserved keyword.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 18:58:57

On Thu, Jan 6, 2022, at 15:05, Pavel Stehule wrote:

>>čt 6. 1. 2022 v 14:28 odesílatel Joel Jacobson <joel@compiler.org> napsal:

>>How about using the existing reserved keyword "in" followed by "." (dot) and then the function parameter name?

>>This idea is based on the assumption "in." would always be a syntax error everywhere in both SQL and PL/pgSQL,

>>so if we would introduce such a syntax, no existing code could be affected, except currently invalid code.

>>I wouldn't mind using "in." to refer to IN/OUT/INOUT parameters and not only IN ones, it's a minor confusion that could be >>explained in the docs.

>You are right, in.outvar looks messy.

I think you misunderstood what I meant, I suggested "in.outvar" would actually be OK.

> Moreover, maybe the SQL parser can have a problem with it.

How could the SQL parser have a problem with it, if "in" is currently never followed by "." (dot)?

Not an expert in the SQL parser, trying to understand why it would be a problem.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 19:10:29

čt 6. 1. 2022 v 16:59 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 15:05, Pavel Stehule wrote:
>>čt 6. 1. 2022 v 14:28 odesílatel Joel Jacobson <joel@compiler.org> napsal:
>>How about using the existing reserved keyword "in" followed by "." (dot) and then the function parameter name?
>>
>>This idea is based on the assumption "in." would always be a syntax error everywhere in both SQL and PL/pgSQL,
>>so if we would introduce such a syntax, no existing code could be affected, except currently invalid code.
>>
>>I wouldn't mind using "in." to refer to IN/OUT/INOUT parameters and not only IN ones, it's a minor confusion that could be >>explained in the docs.
>
>You are right, in.outvar looks messy.

I think you misunderstood what I meant, I suggested "in.outvar" would actually be OK.

I understand well, and I don't think it's nice.

Are there some similar features in other programming languages?

> Moreover, maybe the SQL parser can have a problem with it.

How could the SQL parser have a problem with it, if "in" is currently never followed by "." (dot)?
Not an expert in the SQL parser, trying to understand why it would be a problem.

you can check it. It is true, so IN is usually followed by "(", but until check I am not able to say if there will be an unwanted shift or collision or not.

Regards

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 19:47:48

On Thu, Jan 6, 2022, at 17:10, Pavel Stehule wrote:

> I understand well, and I don't think it's nice.

> Are there some similar features in other programming languages?

It would be similar to "this" in Javascript/Java/C++,

but instead using "in" to access function parameters.

Currently, we need to prefix the parameter name if it's in conflict with a column name:

CREATE FUNCTION very_long_function_name(id int, some_value text)

RETURNS boolean

LANGUAGE sql AS $$

UPDATE some_table

SET some_value = very_long_function_name.some_value

WHERE id = very_long_function_name.id RETURNING TRUE

$$;

This is cumbersome as function names can be long, and if changing the function name,

you would need to update all occurrences of the function name in the code.

If we could instead refer to the parameters by prefixing them with "in.", we could write:

CREATE FUNCTION very_long_function_name(id int, some_value text)

RETURNS boolean

LANGUAGE sql AS $$

UPDATE some_table

SET some_value = in.some_value

WHERE id = in.id RETURNING TRUE

$$;

I think this would be nice, even if it would only work for IN parameters,

since you seldom need to access OUT parameters in the problematic WHERE-clauses anyway.

I mostly use OUT parameters when setting them on a separate row:

some_out_var := some_value;

...or, when SELECTin INTO an OUT parameter, which wouldn't be a problem.

> you can check it. It is true, so IN is usually followed by "(", but until check I am not able to say if there will be an unwanted

> shift or collision or not.

I checked gram.y, and IN_P is never followed by '.', but not sure if it could cause problems anyway, hope someone with parser knowledge can comment on this.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Tom Lane

Date:

06 January 2022, 19:55:03

Pavel Stehule <pavel.stehule@gmail.com> writes:
> čt 6. 1. 2022 v 16:59 odesílatel Joel Jacobson <joel@compiler.org> napsal:
>> How could the SQL parser have a problem with it, if "in" is currently
>> never followed by "." (dot)?

> you can check it. It is true, so IN is usually followed by "(", but until
> check I am not able to say if there will be an unwanted shift or collision
> or not.

Even if it works today, we could be letting ourselves in for future
trouble.  The SQL standard is a moving target, and they could easily
standardize some future syntax involving IN that creates a problem here.

I think we already have two perfectly satisfactory answers:
* qualify parameters with the function name to disambiguate them;
* use the ALIAS feature to create an internal, shorter name.
I see no problem here that is worth locking ourselves into unnecessary
syntax assumptions to fix.

            regards, tom lane

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 20:17:55

On Thu, Jan 6, 2022, at 17:55, Tom Lane wrote:

> Even if it works today, we could be letting ourselves in for future

> trouble. The SQL standard is a moving target, and they could easily

> standardize some future syntax involving IN that creates a problem here.

Perhaps the "in." notation could be standardized by the SQL standard,

allowing vendors to use such notation without fear of future trouble?

> I think we already have two perfectly satisfactory answers:

> * qualify parameters with the function name to disambiguate them;

> * use the ALIAS feature to create an internal, shorter name.

I would say we have two OK workarounds, far from perfect:

* Qualifying parameters is too verbose. Function names can be long.

* Having to remap parameters using ALIAS is cumbersome.

This problem is one of my top annoyances with PL/pgSQL.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 21:03:47

čt 6. 1. 2022 v 17:48 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 17:10, Pavel Stehule wrote:
> I understand well, and I don't think it's nice.
>
> Are there some similar features in other programming languages?

It would be similar to "this" in Javascript/Java/C++,
but instead using "in" to access function parameters.

Currently, we need to prefix the parameter name if it's in conflict with a column name:

CREATE FUNCTION very_long_function_name(id int, some_value text)
RETURNS boolean
LANGUAGE sql AS $$
UPDATE some_table
SET some_value = very_long_function_name.some_value
WHERE id = very_long_function_name.id RETURNING TRUE
$$;

This is cumbersome as function names can be long, and if changing the function name,
you would need to update all occurrences of the function name in the code.

If we could instead refer to the parameters by prefixing them with "in.", we could write:

CREATE FUNCTION very_long_function_name(id int, some_value text)
RETURNS boolean
LANGUAGE sql AS $$
UPDATE some_table
SET some_value = in.some_value
WHERE id = in.id RETURNING TRUE
$$;

I think this would be nice, even if it would only work for IN parameters,
since you seldom need to access OUT parameters in the problematic WHERE-clauses anyway.
I mostly use OUT parameters when setting them on a separate row:
some_out_var := some_value;
...or, when SELECTin INTO an OUT parameter, which wouldn't be a problem.

There is full agreement in a description of the issue. Just I don't like the proposed solution. The word "in '' is not too practical (where there are out, and inout) - and it goes against the philosophy of ADA, where all labels are parametrized (there is not any buildin label). The ADA language has two things that plpgsql has not (unfortunately): a) possibility to modify (configure) compiler by PRAGMA directive, b) possibility to define PRAGMA on more levels - package, function, block. The possibility to define a label dynamically is a better solution (not by some buildin keyword), because it allows some possibility for the end user to define what he prefers. For some cases "in" can be ok, but when you have only two out variables, then "in" looks a little bit strange, and I prefer "fx", other people can prefer "a" or "args".

There is no technical problem in the definition of alias of top namespace. The problem is in syntax and in forcing this setting to some set of routines. Theoretically we can have GUC plpgsql.rootns. I can set there "fx", and you can set there "in" and we both can be happy. But the real question is - how to force this setting to all functions. GUC can be overwritten in session, and although you can set this GUC in every function (by option or by assigned GUC), it is not nice, and somebody can forget about this setting. For me, there are more interesting (important) issues than the possibility to specify the root namespace that can be nice to control. I miss some configuration mechanism independent of GUC that is static (and that emulates static compile options), that can be assigned to database (as synonym for project) or schema (as synonym for module or package). With this mechanism this thread will be significantly shorter, and all discussion about plpgsql2 was not.

> you can check it. It is true, so IN is usually followed by "(", but until check I am not able to say if there will be an unwanted
> shift or collision or not.

I checked gram.y, and IN_P is never followed by '.', but not sure if it could cause problems anyway, hope someone with parser knowledge can comment on this.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 21:28:44

čt 6. 1. 2022 v 18:18 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 17:55, Tom Lane wrote:
> Even if it works today, we could be letting ourselves in for future
> trouble. The SQL standard is a moving target, and they could easily
> standardize some future syntax involving IN that creates a problem here.

Perhaps the "in." notation could be standardized by the SQL standard,
allowing vendors to use such notation without fear of future trouble?

> I think we already have two perfectly satisfactory answers:
> * qualify parameters with the function name to disambiguate them;
> * use the ALIAS feature to create an internal, shorter name.

I would say we have two OK workarounds, far from perfect:
* Qualifying parameters is too verbose. Function names can be long.
* Having to remap parameters using ALIAS is cumbersome.

This problem is one of my top annoyances with PL/pgSQL.

It depends on the programming style. I am accepting so this can be a real issue, although I have had this necessity only a few times, and I have not received any feedback from customers about it.

Probably there can be an agreement so somebody uses it more often and somebody only a few times. If you need it every time, then you need some global solution. If you use it only a few times, then a local verbose solution will be prefered, and a global solution can be the source of new issues.

Maybe they can help if we accept that there are two different problems, and we should design two different solutions.

1. how to set globally plpgsql root namespace

2. how to set locally plpgsql root namespace

@1 can be solved by (very dirty) GUC plpggsql.root_ns_alias (this is just first shot, nothing more)

@2 can be solved by #option root_ns_alias or by extending DECLARE by syntax "fx ALIAS FOR LABEL functioname

I accept that both issues are valid, and I don't think we can find a good design that solves both issues, because there are different objective expectations.

Regards

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 21:32:54

I accept that both issues are valid, and I don't think we can find a good design that solves both issues, because there are different objective expectations.

I accept that both issues are valid, and I don't think we can find a **one** good design that solves both issues, because there are different objective expectations.

Regards

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 22:02:03

On Thu, Jan 6, 2022, at 19:03, Pavel Stehule wrote:

> The possibility to define a label dynamically is a better solution (not by some buildin keyword),

> because it allows some possibility for the end user to define what he prefers.

I'm trying to understand why you think a user-defined notation is desirable,

why it wouldn't be better if the SQL standard would endorse a notation,

so we could all write code in the same way, avoiding ugly GUCs or PRAGMAs altogether?

If "in." would work, due to "in" being a reserved SQL keyword,

don't you think the benefits of a SQL standardized solution would outweigh our

personal preferences on what word each one of us prefer?

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 22:24:17

čt 6. 1. 2022 v 20:03 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 19:03, Pavel Stehule wrote:
> The possibility to define a label dynamically is a better solution (not by some buildin keyword),
> because it allows some possibility for the end user to define what he prefers.

I'm trying to understand why you think a user-defined notation is desirable,
why it wouldn't be better if the SQL standard would endorse a notation,
so we could all write code in the same way, avoiding ugly GUCs or PRAGMAs altogether?

But there is nothing similar in standard. Standard doesn't specify any column or table or label names in the custom area.

The ADA language, an PL/SQL origin, and the PL/pgSQL origin has not nothing similar too. Moreover it (ADA language) was designed as a safe, very verbose language without implicit conventions.

I think we have different positions, because we see different usage, based on, probably, a different programming style. I can understand the request for special common notation for access for routine parameters. But the "in" keyword for this case is not good, and I really think it is better to give some freedom to the user to choose their own label, if we don't know the best one.

If "in." would work, due to "in" being a reserved SQL keyword,
don't you think the benefits of a SQL standardized solution would outweigh our
personal preferences on what word each one of us prefer?

I know that "in" is a reserved word in SQL, but I have not any knowledge of it being used as alias in SQL functions or in SQL/PSM functions.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

06 January 2022, 23:04:17

On Thu, Jan 6, 2022, at 20:24, Pavel Stehule wrote:

> But there is nothing similar in standard.

> Standard doesn't specify any column or table or label names in the custom area.

I think that's an unfair comparison.

This isn't at all the same thing as dictating column or table names.

I merely suggest reusing an existing reserved keyword.

>>čt 6. 1. 2022 v 20:03 odesílatel Joel Jacobson <joel@compiler.org> napsal:

>>If "in." would work, due to "in" being a reserved SQL keyword,

>>don't you think the benefits of a SQL standardized solution would outweigh our

>>personal preferences on what word each one of us prefer?

>I know that "in" is a reserved word in SQL, but I have not any knowledge of it being used as alias in SQL functions or in >SQL/PSM functions.

Are you concerned "in" might already be used as an alias somehow?

I did some testing:

"in" can be used as a column alias:

=> SELECT 123 AS in;

-----

123

(1 row)

But it cannot be used as a table alias, which is what matters:

=> WITH in AS (SELECT 1) SELECT * FROM in;

ERROR: syntax error at or near "in"

=> SELECT * FROM t in;

ERROR: syntax error at or near "in"

=> SELECT * FROM t AS in;

ERROR: syntax error at or near "in"

It's also currently not possible to use it as a PL/pgSQL alias:

=> CREATE FUNCTION f(id int)

RETURNS void

LANGUAGE plpgsql AS $$

DECLARE

in ALIAS FOR $1;

BEGIN

END

$$;

ERROR: syntax error at or near "in"

LINE 5: in ALIAS FOR $1;

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

06 January 2022, 23:38:38

čt 6. 1. 2022 v 21:04 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Thu, Jan 6, 2022, at 20:24, Pavel Stehule wrote:
> But there is nothing similar in standard.
> Standard doesn't specify any column or table or label names in the custom area.

I think that's an unfair comparison.
This isn't at all the same thing as dictating column or table names.
I merely suggest reusing an existing reserved keyword.

>>čt 6. 1. 2022 v 20:03 odesílatel Joel Jacobson <joel@compiler.org> napsal:
>>
>>If "in." would work, due to "in" being a reserved SQL keyword,
>>don't you think the benefits of a SQL standardized solution would outweigh our
>>personal preferences on what word each one of us prefer?
>
>I know that "in" is a reserved word in SQL, but I have not any knowledge of it being used as alias in SQL functions or in >SQL/PSM functions.

Are you concerned "in" might already be used as an alias somehow?

I did not fully correct sentence. I wanted to write "alias of arguments of SQL functions or SQL/PSM functions". I am sorry.

I did some testing:

"in" can be used as a column alias:

=> SELECT 123 AS in;
in
-----
123
(1 row)

But it cannot be used as a table alias, which is what matters:

=> WITH in AS (SELECT 1) SELECT * FROM in;
ERROR: syntax error at or near "in"

=> SELECT * FROM t in;
ERROR: syntax error at or near "in"

=> SELECT * FROM t AS in;
ERROR: syntax error at or near "in"

It's also currently not possible to use it as a PL/pgSQL alias:

=> CREATE FUNCTION f(id int)
RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
in ALIAS FOR $1;
BEGIN
END
$$;
ERROR: syntax error at or near "in"
LINE 5: in ALIAS FOR $1;

I didn't say, so "IN" cannot be used. Technically, maybe (I didn't write a patch). I say, semantically - how I understand the meaning of the word "in" is not good to use it for generic alias of function arguments (when we have out arguments too).

Pavel

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

"Joel Jacobson"

Date:

07 January 2022, 00:12:44

On Thu, Jan 6, 2022, at 21:38, Pavel Stehule wrote:

> I say, semantically - how I understand the meaning of the word "in" is not good to use it for generic alias of function arguments (when we have out arguments too).

Trying to imagine a situation where you would need a shorthand also for OUT parameters in real-life PL/pgSQL function.

Can you give an example?

I can only think of situations where it is needed for IN parameters.

/Joel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

05 March 2022, 11:54:18

On Thu, Jan 06, 2022 at 05:05:32PM +0800, Julien Rouhaud wrote:
>
> Anyway, the only committer that showed some interest in the feature is Michael,
> and he seemed ok in principle with the "alias-implementation" approach.
> Michael, did you have a look at this version ([1]), or do you think it should
> simply be rejected?

After a couple of months I see that there is unfortunately no agreement on this
patch, or even its need.

I think we should close the patch as Rejected, and probably add an entry in the
"Feature we do not want" wiki page.

I will do that in a few days unless someone shows up with new arguments.

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Michael Paquier

Date:

05 March 2022, 13:31:53

On Sat, Mar 05, 2022 at 04:54:18PM +0800, Julien Rouhaud wrote:
> On Thu, Jan 06, 2022 at 05:05:32PM +0800, Julien Rouhaud wrote:
>> Anyway, the only committer that showed some interest in the feature is Michael,
>> and he seemed ok in principle with the "alias-implementation" approach.
>> Michael, did you have a look at this version ([1]), or do you think it should
>> simply be rejected?
>
> After a couple of months I see that there is unfortunately no agreement on this
> patch, or even its need.

I got a short look at what was proposed in the patch a couple of
months ago, and still found the implementation confusing with the way
aliases are handled, particularly when it came to several layers of
pl/pgsql.  I am fine to let it go for now.
--
Michael

Attachment

signature.asc

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Michael Paquier

Date:

07 March 2022, 05:27:14

On Sat, Mar 05, 2022 at 07:31:53PM +0900, Michael Paquier wrote:
> I got a short look at what was proposed in the patch a couple of
> months ago, and still found the implementation confusing with the way
> aliases are handled, particularly when it came to several layers of
> pl/pgsql.  I am fine to let it go for now.

Just had an extra look at the patch, still same impression.  So done
this way.
--
Michael

Attachment

signature.asc

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

07 March 2022, 05:31:40

On Mon, Mar 07, 2022 at 11:27:14AM +0900, Michael Paquier wrote:
> On Sat, Mar 05, 2022 at 07:31:53PM +0900, Michael Paquier wrote:
> > I got a short look at what was proposed in the patch a couple of
> > months ago, and still found the implementation confusing with the way
> > aliases are handled, particularly when it came to several layers of
> > pl/pgsql.  I am fine to let it go for now.
>
> Just had an extra look at the patch, still same impression.  So done
> this way.

I was actually waiting a bit to  make sure that Pavel could read the thread,
since it was the weekend and right now it's 3:30 AM in Czech Republic...

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Michael Paquier

Date:

07 March 2022, 08:06:43

On Mon, Mar 07, 2022 at 10:31:40AM +0800, Julien Rouhaud wrote:
> I was actually waiting a bit to  make sure that Pavel could read the thread,
> since it was the weekend and right now it's 3:30 AM in Czech Republic...

Sorry about that.  I have reset the state of the patch.
--
Michael

Attachment

signature.asc

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Pavel Stehule

Date:

07 March 2022, 08:35:45

po 7. 3. 2022 v 3:31 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

On Mon, Mar 07, 2022 at 11:27:14AM +0900, Michael Paquier wrote:
> On Sat, Mar 05, 2022 at 07:31:53PM +0900, Michael Paquier wrote:
> > I got a short look at what was proposed in the patch a couple of
> > months ago, and still found the implementation confusing with the way
> > aliases are handled, particularly when it came to several layers of
> > pl/pgsql. I am fine to let it go for now.
>
> Just had an extra look at the patch, still same impression. So done
> this way.

I was actually waiting a bit to make sure that Pavel could read the thread,
since it was the weekend and right now it's 3:30 AM in Czech Republic...

this patch should be rejected. There is no consensus.

Regards

Pavel

Re: pl/pgsql feature request: shorthand for argument and local variable references

From

Julien Rouhaud

Date:

07 March 2022, 08:48:55

On Mon, Mar 07, 2022 at 06:35:45AM +0100, Pavel Stehule wrote:
> 
> this patch should be rejected. There is no consensus.

Thanks for the confirmation, I will take care of it!