Re: plpgsql and qualified variable names

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

Tom Lane wrote:
> 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.

If I'm understanding that correctly, Oracle would resolve the reference
to "ambiguous" in the function below to column in table foo, but allows
you to reference the parameter instead by specifying
"somefunc.ambiguous", while we always resolve it to the parameter.

CREATE TABLE foo (ambiguous integer);

CREATE FUNCTION somefunc(ambiguous integer) RETURNS integer AS $$
DECLARE
SELECT ambiguous FROM foo;
...

ISTM supporting "somefunc.ambiguous" just gives us another way to
reference the parameter, and there still isn't any way to refer the column.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> ISTM supporting "somefunc.ambiguous" just gives us another way to
> reference the parameter, and there still isn't any way to refer the column.

Sure. All this will do is let us remove a noted incompatibility with
Oracle, which seems worth doing if it's a one-line change that doesn't
break anything.

Further down the road, we could imagine some option in plpgsql that
prevents substitution of variables *unless* they are qualified with
the appropriate block name --- in which case we'd better make sure
there is a way to qualify function parameter names. So this might
be a necessary component of a solution that tightens up the
substitution behavior, but it's not the solution by itself.

regards, tom lane

> ISTM supporting "somefunc.ambiguous" just gives us another way to
> reference the parameter, and there still isn't any way to refer the
> column.

Could we not, at least, support explicit column disambiguation?

e.g. This PL/SQL procedure:

CREATE OR REPLACE PROCEDURE insert_emp (empno emp.empno%TYPE,
ename emp.ename%TYPE) IS
BEGIN
INSERT INTO emp (empno, ename) VALUES (empno, ename);
END;

is tantamount to writing this:

CREATE OR REPLACE PROCEDURE insert_emp (empno emp.empno%TYPE,
ename emp.ename%TYPE) IS
BEGIN
INSERT INTO emp (emp.empno, emp.ename)
VALUES (insert_emp.empno, insert_emp.ename);
END;

Both are valid, and notice how the latter evinces disambiguation
supported both ways.

--
Affan Salman
EnterpriseDB Corporation http://www.enterprisedb.com

"Affan Salman" <affan(at)enterprisedb(dot)com> writes:
> Could we not, at least, support explicit column disambiguation?

The problem is that there are places in the SQL grammar where we don't
allow qualification of a SQL name --- INSERT column lists, UPDATE SET
targets, and SELECT AS labels are three I can think of offhand.
Without fixing that it's a bit tough, and in at least the UPDATE
case there are severe ambiguity problems if we try to allow a noise
qualification.

In at least those three cases, we know that it's not sensible to
substitute a parameter. If that's true in all the problem cases,
which seems likely, then we could do something with Greg's idea
of using the raw parse tree from the main SQL parser to guide
decisions about where parameters may be substituted. I complained
earlier about the loss of a printable representation of the
substituted query, but we'd not necessarily have to give that up.
Seeing that ColumnRef carries a pointer back into the source text,
we could use the ColumnRefs to drive a textual substitution and
not have to change that aspect of the API.

regards, tom lane

>
> 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.
>

it's not supported yet?

postgres=# create or replace function foox(a integer) returns integer
as $$ begin return foox.a; end $$ language plpgsql;
CREATE FUNCTION

ostgres=# select foox(10);
ERROR: missing FROM-clause entry for table "foox"
LINE 1: SELECT foox.a
^
QUERY: SELECT foox.a
CONTEXT: PL/pgSQL function "foox" line 1 at return

I am sure, It's good idea - and I thing SQL/PSM specifies it too.

Regards
Pavel Stehule

>
> In at least those three cases, we know that it's not sensible to
> substitute a parameter. If that's true in all the problem cases,
> which seems likely, then we could do something with Greg's idea
> of using the raw parse tree from the main SQL parser to guide
> decisions about where parameters may be substituted. I complained
> earlier about the loss of a printable representation of the
> substituted query, but we'd not necessarily have to give that up.
> Seeing that ColumnRef carries a pointer back into the source text,
> we could use the ColumnRefs to drive a textual substitution and
> not have to change that aspect of the API.
>

Variables substitution is probable them most big hack on plpgsql. I am
not sure, so this is well solution. We can generate more helpful hint
and that is all.

Regards
Pavel Stehule

On Sat, 2007-07-14 at 19:52 -0400, Tom Lane wrote:
> "Affan Salman" <affan(at)enterprisedb(dot)com> writes:
> > Could we not, at least, support explicit column disambiguation?
>
> The problem is that there are places in the SQL grammar where we don't
> allow qualification of a SQL name --- INSERT column lists, UPDATE SET
> targets, and SELECT AS labels are three I can think of offhand.
> Without fixing that it's a bit tough, and in at least the UPDATE
> case there are severe ambiguity problems if we try to allow a noise
> qualification.
>
> In at least those three cases, we know that it's not sensible to
> substitute a parameter. If that's true in all the problem cases,
> which seems likely, then we could do something with Greg's idea
> of using the raw parse tree from the main SQL parser to guide
> decisions about where parameters may be substituted. I complained
> earlier about the loss of a printable representation of the
> substituted query, but we'd not necessarily have to give that up.
> Seeing that ColumnRef carries a pointer back into the source text,
> we could use the ColumnRefs to drive a textual substitution and
> not have to change that aspect of the API.

Well, I think we need this, eventually.

It's just weird to have pseudo random failures in CREATE FUNCTION
because the naming of function developers happens to coincide with the
naming of DBAs.

BTW, much clearer docs, thanks.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

I wrote:
> ... In at least those three cases, we know that it's not sensible to
> substitute a parameter. If that's true in all the problem cases,
> which seems likely, then we could do something with Greg's idea
> of using the raw parse tree from the main SQL parser to guide
> decisions about where parameters may be substituted.

BTW, I looked into this a bit and found that it doesn't seem to be a
simple change. Basically what we'd want to do is take out the current
behavior in which the plpgsql lexer knows anything about which
identifiers are plpgsql variables, and have the substitutions happen
later, after we've run the collected statement or expression through
the main grammar. The problem is that doing so breaks the recognition
of plpgsql assignment statements, since the grammar currently keys off
the fact that the first token of the statement has been recognized to
match a variable name by the lexer. I'm not sure what's the most
reasonable way to fix that, but at the moment it looks like we're
talking about a pretty thoroughgoing redesign of the plpgsql lexer
and parser.

Anyway it seems like this whole area is a research project for 8.4
or later, not something we should try to fix now. But having said
that, there didn't seem to be any objection to the idea of changing
the outer block (where function parameters are declared) to be labeled
with the function name, instead of having no label as at present.
Does anyone think we should not do that for 8.3?

regards, tom lane

> Anyway it seems like this whole area is a research project for 8.4
> or later, not something we should try to fix now. But having said
> that, there didn't seem to be any objection to the idea of changing
> the outer block (where function parameters are declared) to be labeled
> with the function name, instead of having no label as at present.
> Does anyone think we should not do that for 8.3?
>

I am for 8.3

Regards
Pavel Stehule

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

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

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://www.enterprisedb.com

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

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. +