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
RAISENOTICE '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
dothis 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