Обсуждение: pl/pgsql feature request: shorthand for argument and local variable references

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

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.

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;

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

ú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

 

(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

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

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

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

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

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!

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;

Cheers
Hannu

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

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

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

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

> 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

+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

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

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.

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.

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?

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

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

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

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

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

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

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

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