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

Hi

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

> When arguments and other local variables in pl/pgsql functions have the
> same name as columns referenced in queries it is necessary to disambiguate
> the names. This can be done by prefixing the function name (e.g.
> my_func.name), using the argument number is the case of an argument (e.g.
> $1), or renaming the variable (e.g. _name). It is also possible to use a
> GUC to always use the variable or the column but that seems dangerous to me.
>
> Prefixing names with an underscore works well enough for local variables,
> but when using named arguments I prefer the external name not require an
> underscore. I would like to suggest a standard prefix such as $ to
> reference a local variable or argument. $ followed by an integer already
> references an argument by ordinal. What if $ followed by a name meant a
> local reference (essentially it would expand to "my_func.")?
>
> For example, currently I have to do something like this:
>
> create function update_item(id int, foo int, bar text) returns void
> language plpgsql as $$
> begin
> update items
> set foo = update_item.foo,
> bar = update_item.bar
> where items.id = update_item.id;
> end;
> $$;
>
> I would like to be able to do something like:
>
> create function update_item(id int, foo int, bar text) returns void
> language plpgsql as $$
> begin
> update items
> set foo = $foo,
> bar = $bar
> where items.id = $id;
> end;
> $$;
>
> Any opinions on the desirability of this feature? My C skills are rather
> atrophied, but from the outside it seems like a small enough change I might
> be able to tackle it...
>

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

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

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

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

We already can do

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

or safer

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

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

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

Regards

Pavel

> Jack
>