Hi út 17. 11. 2020 v 19:04 odesílatel Jack Christensen <j...@jncsoftware.com> napsal:
> When arguments and other local variables in pl/pgsql functions have the > same name as columns referenced in queries it is necessary to disambiguate > the names. This can be done by prefixing the function name (e.g. > my_func.name), using the argument number is the case of an argument (e.g. > $1), or renaming the variable (e.g. _name). It is also possible to use a > GUC to always use the variable or the column but that seems dangerous to me. > > Prefixing names with an underscore works well enough for local variables, > but when using named arguments I prefer the external name not require an > underscore. I would like to suggest a standard prefix such as $ to > reference a local variable or argument. $ followed by an integer already > references an argument by ordinal. What if $ followed by a name meant a > local reference (essentially it would expand to "my_func.")? > > For example, currently I have to do something like this: > > create function update_item(id int, foo int, bar text) returns void > language plpgsql as $$ > begin > update items > set foo = update_item.foo, > bar = update_item.bar > where items.id = update_item.id; > end; > $$; > > I would like to be able to do something like: > > create function update_item(id int, foo int, bar text) returns void > language plpgsql as $$ > begin > update items > set foo = $foo, > bar = $bar > where items.id = $id; > end; > $$; > > Any opinions on the desirability of this feature? My C skills are rather > atrophied, but from the outside it seems like a small enough change I might > be able to tackle it... > I don't like this proposal too much. Introducing the next different syntax for writing local variables doesn't look like a good idea for me. More this syntax is used by very different languages than is PL/pgSQL, and then it can be messy. The behaviour of local variables in PHP or Perl or shell is really very different. Personally in your example I very much like notation "update_item.id", because there is a clean signal so "id" is the function's argument. When you use "$id", then it is not clean if "id" is a local variable or function's argument. So your proposal decreases safety :-/. Plus this syntax reduces collision only on one side, you should use aliases for sql identifiers and again it is not balanced - In MS SQL I can write predicate id = @id. But it is not possible in your proposal (and it is not possible from compatibility reasons ever). More we already has a possibility to do ALIAS of any variable https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS I understand that there can be problems with functions with very long names. We already can do CREATE OR REPLACE FUNCTION public.fx(par1 integer, par2 integer) RETURNS void LANGUAGE plpgsql AS $function$ <<b>> declare p1 alias for par1; p2 alias for par2; begin raise notice '% % % %', par1, par2, b.p1, b.p2; end; $function$ or safer CREATE OR REPLACE FUNCTION public.fx(par1 integer, par2 integer) RETURNS void LANGUAGE plpgsql AS $function$ <<b>> declare p1 alias for fx.par1; p2 alias for fx.par2; begin raise notice '% % % %', par1, par2, b.p1, b.p2; end; $function$ So I think introducing new syntax is not necessary. The open question is a possibility to do aliasing more comfortably. ADA language has a possibility to rename function or procedure. But it is much more stronger, than can be implemented in plpgsql. Probably the most easy implementation can be a possibility to specify a new argument's label with already supported #option syntax. CREATE OR REPLACE FUNCTION very_long_name(par1 int) RETURNS int AS $$ #routine_label lnm BEGIN RAISE NOTICE '%', lnm.par1; Regards Pavel > Jack >
