This has been saved for the 8.4 release:


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 >>
>     quantity integer := 30;
>     RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
>     quantity := 50;
>     --
>     -- Create a subblock
>     --
>         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

  Bruce Momjian  <[EMAIL PROTECTED]>

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to