Thread: ambiguous local variable name in 9.0 proc
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Samuel Gendler
Sent: Wednesday, September 21, 2011 7:35 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] ambiguous local variable name in 9.0 proc
I've got a stored proc (which worked fine in 8.3 and 8.4) that is declared as such:
CREATE OR REPLACE FUNCTION reporting_mgmt.aggregate_timescales_impl (
div_start TIMESTAMP WITHOUT TIME ZONE,
tbl_schema VARCHAR,
tbl_root VARCHAR,
fine_timescale VARCHAR,
coarse_timescale VARCHAR,
coarser_timescale VARCHAR,
fact_fields VARCHAR,
dim_fields VARCHAR,
sum_fields VARCHAR)
RETURNS INTEGER
AS $$
Within that proc, I've got the following line:
IF EXISTS ( SELECT table_name FROM information_schema.tables
WHERE table_schema = tbl_schema
AND table_name = tbl_fine_part_old ) THEN
IF EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e
WHERE tbl_schema = e.tbl_schema
AND tbl_root = e.tbl_root
AND div_start_old = e.fine_time
AND coarse_timescale = e.coarse_scale
AND status = 0 ) THEN
And in 9.0, it is generating the following error:
ERROR: column reference "tbl_schema" is ambiguous
LINE 2: WHERE tbl_schema = e.tbl_schema
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e
WHERE tbl_schema = e.tbl_schema
AND tbl_root = e.tbl_root
AND div_start_old = e.fine_time
AND coarse_timescale = e.coarse_scale
AND status = 0 )
CONTEXT: PL/pgSQL function "aggregate_timescales_impl" line 52 at IF
PL/pgSQL function "aggregate_timescales" line 23 at RETURN
Of course, it is true that tbl_schema could refer to the column in table etl_status, except older versions of postgres seemed to correctly figure out that comparing a column to itself isn't terribly useful, so it must be referring to the pl/pgsql variable rather than the table column.
I'm happy to modify the proc definition, except that I am unsure how to do so other than to rename the variable, which is my least favourite way to do that. I'd far rather qualify the name somehow, so that it knows that I am refering to a local variable, if at all possible. Suggestions?
Not tested but I think all local variables are implicitly scoped to the function name so you should be able to do the following:
WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema
You are going to have the same problem with other fields as well (e.g., tbl_root).
I believe there is some way to define the function so that it does not throw that particular error; it would be up to you make sure that the ambiguity is being resolved correctly (which it should in this particular case). Maybe look for “SET” variables.
You could also copy the tbl_schema variable value into a different variable.
DECLARE tbl_schema_copy VARCHAR; tbl_schema_copy := tbl_schema;
… WHERE tbl_schema_copy = e.tbl_schema …
David J.
Not tested but I think all local variables are implicitly scoped to the function name so you should be able to do the following:
WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema
You can also set the behavior on a function-by-function basis, by inserting one of these special commands at the start of the function text:
#variable_conflict error #variable_conflict use_variable
"David Johnston" <polobo@yahoo.com> writes: > On Behalf Of Samuel Gendler > I'm happy to modify the proc definition, except that I am unsure how to do > so other than to rename the variable, which is my least favourite way to do > that. I'd far rather qualify the name somehow, so that it knows that I am > refering to a local variable, if at all possible. Suggestions? > Not tested but I think all local variables are implicitly scoped to the > function name so you should be able to do the following: > WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema More accurately: you can qualify a local variable's name with the label attached to the block in which it's declared; or with the function's name if the variable is a function parameter. See the fine manual: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html > I believe there is some way to define the function so that it does not throw > that particular error; it would be up to you make sure that the ambiguity is > being resolved correctly (which it should in this particular case). Yes, see variable_conflict in http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html I wouldn't really recommend turning off the conflict detection, though. We put it in because of the number of hours people had wasted on unrecognized conflicts. regards, tom lane