Re: [HACKERS] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql and qualified variable names
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 [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + 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
Re: [HACKERS] plpgsql and qualified variable names
On Sat, 2007-07-14 at 19:52 -0400, Tom Lane wrote: Affan Salman [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql and qualified variable names
Heikki Linnakangas [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql and qualified variable names
Affan Salman [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql and qualified variable names
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
