Re: pl/pgsql feature request: shorthand for argument and local variable references - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: pl/pgsql feature request: shorthand for argument and local variable references |
Date | |
Msg-id | CAFj8pRA__0qKF2wg-A=+Vh-a4kdLhRp=+jvsRBr5x=MJx6t4Yg@mail.gmail.com Whole thread Raw |
In response to | pl/pgsql feature request: shorthand for argument and local variable references (Jack Christensen <jack@jncsoftware.com>) |
Responses |
Re: pl/pgsql feature request: shorthand for argument and local variable references
(Jack Christensen <jack@jncsoftware.com>)
|
List | pgsql-hackers |
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$
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$
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
pgsql-hackers by date: