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
