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

Hi

ú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

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

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]https://www.postgresql.org/docs/13/sql-keywords-appendix.html#KEYWORDS-TABLE 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

ú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

Pavel

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)

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

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

Hi

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

Hi

fix broken regress test

Regards

Pavel

I think that this can be a useful feature in many occasions, especially since
the overhead is almost inexistent.

The implementation is sensible and there are regression tests to cover the new
feature.

There's a problem however if you try to add multiple #routine_label commands in
the same function. At minimum the Assert on "nse->itemno == (int)
PLPGSQL_LABEL_BLOCK" will fail.

I don't think that it makes sense to have multiple occurences of this command,
and we should simply error out if plpgsql_curr_compile->root_ns->itemno is
PLPGSQL_LABEL_REPLACED. If any case this should also be covered in the
regression tests.

Hi

so 13. 3. 2021 v 9:53 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

I think that this can be a useful feature in many occasions, especially
since
the overhead is almost inexistent.

The implementation is sensible and there are regression tests to cover the
new
feature.

There's a problem however if you try to add multiple #routine_label
commands in
the same function. At minimum the Assert on "nse->itemno == (int)
PLPGSQL_LABEL_BLOCK" will fail.

I don't think that it makes sense to have multiple occurences of this
command,
and we should simply error out if plpgsql_curr_compile->root_ns->itemno is
PLPGSQL_LABEL_REPLACED. If any case this should also be covered in the
regression tests.

I did it. Thank you for check

I wrote few sentences to documentation

Regards

Pavel

On Sat, Mar 13, 2021 at 12:10:29PM +0100, Pavel Stehule wrote:

so 13. 3. 2021 v 9:53 odes�latel Julien Rouhaud <rjuju123@gmail.com> napsal:

I don't think that it makes sense to have multiple occurences of this
command,
and we should simply error out if plpgsql_curr_compile->root_ns->itemno is
PLPGSQL_LABEL_REPLACED. If any case this should also be covered in the
regression tests.

I did it. Thank you for check

Thanks, LGTM.

I wrote few sentences to documentation

Great!

I just had a few cosmetic comments:

+      block can be changed by inserting special command at the start of the function
[...]
+     arguments) is possible with option <literal>routine_label</literal>:
[...]
+				 errhint("The option \"routine_label\" can be used only once in rutine."),
[...]
+-- Check root namespace renaming (routine_label option)

You're sometimes using "command" and "sometimes" option. Should we always use
the same term, maybe "command" as it's already used for #variable_conflict
documentation?

Also

+      variables can be qualified with the function's name. The name of this outer
+      block can be changed by inserting special command at the start of the function
+      <literal>#routine_label new_name</literal>.

It's missing a preposition before "special command". Maybe

+      variables can be qualified with the function's name. The name of this outer
+      block can be changed by inserting a special command
+      <literal>#routine_label new_name</literal> at the start of the function.

+ The function's argument can be qualified by function name:

Should be "qualified with the function name"

+     Sometimes the function name is too long and can be more practical to use
+     some shorter label. An change of label of top namespace (with functions's
+     arguments) is possible with option <literal>routine_label</literal>:

Few similar issues. How about:

Sometimes the function name is too long and *it* can be more practical to use
some shorter label. *The top namespace label can be changed* (*along* with
*the* functions' arguments) *using the option* <literal>routine_label</literal>:

I'm not a native English speaker so the proposes changed may be wrong or not
enough.

Hi

so 13. 3. 2021 v 12:48 odesílatel Julien Rouhaud <rjuju123@gmail.com>
napsal:

On Sat, Mar 13, 2021 at 12:10:29PM +0100, Pavel Stehule wrote:

so 13. 3. 2021 v 9:53 odesílatel Julien Rouhaud <rjuju123@gmail.com>

napsal:

I don't think that it makes sense to have multiple occurences of this
command,
and we should simply error out if

plpgsql_curr_compile->root_ns->itemno is

PLPGSQL_LABEL_REPLACED. If any case this should also be covered in the
regression tests.

I did it. Thank you for check

Thanks, LGTM.

I wrote few sentences to documentation

Great!

I just had a few cosmetic comments:

+      block can be changed by inserting special command at the start of
the function
[...]
+     arguments) is possible with option <literal>routine_label</literal>:
[...]
+                                errhint("The option \"routine_label\" can
be used only once in rutine."),
[...]
+-- Check root namespace renaming (routine_label option)

You're sometimes using "command" and "sometimes" option. Should we always
use
the same term, maybe "command" as it's already used for #variable_conflict
documentation?

Also

+      variables can be qualified with the function's name. The name of
this outer
+      block can be changed by inserting special command at the start of
the function
+      <literal>#routine_label new_name</literal>.

It's missing a preposition before "special command". Maybe

+      variables can be qualified with the function's name. The name of
this outer
+      block can be changed by inserting a special command
+      <literal>#routine_label new_name</literal> at the start of the
function.

+ The function's argument can be qualified by function name:

Should be "qualified with the function name"

+     Sometimes the function name is too long and can be more practical to
use
+     some shorter label. An change of label of top namespace (with
functions's
+     arguments) is possible with option <literal>routine_label</literal>:

Few similar issues. How about:

Sometimes the function name is too long and *it* can be more practical to
use
some shorter label. *The top namespace label can be changed* (*along* with
*the* functions' arguments) *using the option*
<literal>routine_label</literal>:

I'm not a native English speaker so the proposes changed may be wrong or
not
enough.

My English is good enough for taking beer everywhere in the world :) . Ti
is not good, but a lot of people who don't understand to English understand
my simplified fork of English language.

Thank you for check

updated patch attached

Pavel

On Sun, Mar 14, 2021 at 08:41:15PM +0100, Pavel Stehule wrote:

My English is good enough for taking beer everywhere in the world :) . Ti
is not good, but a lot of people who don't understand to English understand
my simplified fork of English language.

I have the same problem. We can talk about it and other stuff while having a
beer sometimes :)

updated patch attached

Thanks, it looks good to me, I'm marking the patch as RFC!

po 15. 3. 2021 v 4:54 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

On Sun, Mar 14, 2021 at 08:41:15PM +0100, Pavel Stehule wrote:

My English is good enough for taking beer everywhere in the world :) . Ti
is not good, but a lot of people who don't understand to English

understand

my simplified fork of English language.

I have the same problem. We can talk about it and other stuff while
having a
beer sometimes :)

:)

updated patch attached

Thanks, it looks good to me, I'm marking the patch as RFC!

Thank you very much

Regards

Pavel

On Mon, Mar 15, 2021 at 05:31:18AM +0100, Pavel Stehule wrote:

Thank you very much

I am not much a fan of the implementation done here. Based on my
understanding of the PL code, the namespace lookup is organized as a
list of items, with the namespace associated to the routine name being
the first one pushed to the list after plpgsql_ns_init() initializes
the top of it. Then, the proposed patch hijacks the namespace lookup
list by doing a replacement of the root label while parsing the
routine and then tweaks the lookup logic when a variable is qualified
(aka name2 != NULL) to bump the namespace list one level higher so as
it does not look after the namespace with the routine name but instead
fetches the one defined by ROUTINE_NAME. That seems rather bug-prone
to me, to say the least.

No changes to plpgsql_compile_inline()?

+       ereport(ERROR,
+               (errcode(ERRCODE_SYNTAX_ERROR),
+                errmsg("redundant option"),
+                errhint("The command \"routine_label\" can be used
only once in rutine."),
+                plpgsql_scanner_errposition(location)));
s/rutine/routine/

+ /* Don't allow repeated redefination of routine label */
redefination?

I am wondering whether it would be better to allow multiple aliases
though, and if it would bring more readability to the routines written
if these are treated equal to the top-most namespace which is the
routine name now, meaning that we would maintain not one, but N top
namespace labels that could be used as aliases of the root one.
--
Michael

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

On Mon, Mar 15, 2021 at 05:31:18AM +0100, Pavel Stehule wrote:

Thank you very much

I am not much a fan of the implementation done here. Based on my
understanding of the PL code, the namespace lookup is organized as a
list of items, with the namespace associated to the routine name being
the first one pushed to the list after plpgsql_ns_init() initializes
the top of it. Then, the proposed patch hijacks the namespace lookup
list by doing a replacement of the root label while parsing the
routine and then tweaks the lookup logic when a variable is qualified
(aka name2 != NULL) to bump the namespace list one level higher so as
it does not look after the namespace with the routine name but instead
fetches the one defined by ROUTINE_NAME. That seems rather bug-prone
to me, to say the least.

I'll check what can be done. I am not sure if it is possible to push a new
label to the same level as the top label.

No changes to plpgsql_compile_inline()?

+       ereport(ERROR,
+               (errcode(ERRCODE_SYNTAX_ERROR),
+                errmsg("redundant option"),
+                errhint("The command \"routine_label\" can be used
only once in rutine."),
+                plpgsql_scanner_errposition(location)));
s/rutine/routine/

+ /* Don't allow repeated redefination of routine label */
redefination?

I'll check it. But inline block has not top label

I am wondering whether it would be better to allow multiple aliases
though, and if it would bring more readability to the routines written
if these are treated equal to the top-most namespace which is the
routine name now, meaning that we would maintain not one, but N top
namespace labels that could be used as aliases of the root one.

I do not have a strong opinion, but I am inclined to disallow this. I am
afraid so code can be less readable.

There is a precedent - SQL doesn't allow you to use table names as
qualifiers when you have an alias.

But it is a very important question. The selected behavior strongly impacts
an implementation.

What is the more common opinion about it? 1. Should we allow the original
top label or not? 2. Should we allow to define more top labels?

Regards

Pavel

On Wed, Mar 17, 2021 at 05:30:30AM +0100, Pavel Stehule wrote:

st 17. 3. 2021 v 4:52 odes�latel Michael Paquier <michael@paquier.xyz>

I am wondering whether it would be better to allow multiple aliases
though, and if it would bring more readability to the routines written
if these are treated equal to the top-most namespace which is the
routine name now, meaning that we would maintain not one, but N top
namespace labels that could be used as aliases of the root one.

I do not have a strong opinion, but I am inclined to disallow this. I am
afraid so code can be less readable.

There is a precedent - SQL doesn't allow you to use table names as
qualifiers when you have an alias.

+1

But it is a very important question. The selected behavior strongly impacts
an implementation.

What is the more common opinion about it? 1. Should we allow the original
top label or not? 2. Should we allow to define more top labels?

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.

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

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

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

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

--

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

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

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

END;

It disallows simple implementation.

Regards

Pavel

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

č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

č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

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:

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

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

--

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

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

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

/Joel

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

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

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

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

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.

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

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.

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

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?

č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

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]/messages/by-id/attachment/120789/plpgsql-routine-label-option-alias-implementation.patch), or do you think it should
simply be rejected?

[1]: /messages/by-id/attachment/120789/plpgsql-routine-label-option-alias-implementation.patch

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

č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

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

č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

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

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

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

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

č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

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.

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

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

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

č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

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

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.

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

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

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

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

Hi

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

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!