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@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +