Re: plpgsql and qualified variable names

Added to pl/pgsql TODO:

o Improve logic of determining if an identifier is a a
variable or column name

http://archives.postgresql.org/pgsql-hackers/2007-07/msg00436.php

---------------------------------------------------------------------------

Tom Lane wrote:
> I have just absorbed the significance of some code that has been in
> plpgsql since day one, but has never been documented anyplace.
> It seems that if you attach a "label" to a statement block in a
> plpgsql function, you can do more with the label than just use it in
> an EXIT statement (as I'd always supposed it was for). You can also use
> the label to qualify the names of variables declared in that block.
> For example, I've extended the example in section 37.3 like this:
>
> CREATE FUNCTION somefunc() RETURNS integer AS $$
> << outerblock >>
> DECLARE
> quantity integer := 30;
> BEGIN
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
> quantity := 50;
> --
> -- Create a subblock
> --
> DECLARE
> quantity integer := 80;
> BEGIN
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
> RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
> END;
>
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
>
> RETURN quantity;
> END;
> $$ LANGUAGE plpgsql;
>
> Now the reason I'm interested in this is that it provides another
> technique you can use to deal with conflicts between plpgsql variable
> names and SQL table/column/function names: you can qualify the variable
> name with the block label when you use it in a SQL command. This is
> not in itself a solution to the conflict problem, because unqualified
> names are still at risk of being resolved the "wrong" way, but it still
> seems worth documenting in the new section I'm writing about variable
> substitution rules.
>
> Anyway, I'm not writing just to point out that we have a previously
> undocumented feature. I notice that the section on porting from Oracle
> PL/SQL mentions
>
> You cannot use parameter names that are the same as columns that are
> referenced in the function. Oracle allows you to do this if you qualify
> the parameter name using function_name.parameter_name.
>
> While i haven't tested yet, I believe that we could match this Oracle
> behavior with about a one-line code change: the outermost namespace
> level ("block") that the function parameter aliases are put into just
> needs to be given a label equal to the function name, instead of being
> label-less as it currently is.
>
> Comments? Also, can anyone verify whether this labeling behavior
> matches Oracle?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +