ambiguous local variable name in 9.0 proc - Mailing list pgsql-sql

From Samuel Gendler
Subject ambiguous local variable name in 9.0 proc
Date
Msg-id CAEV0TzB5GDuaG=7OOv_7m6OWagAkkUmkeh=1a62E46o4wKdtgg@mail.gmail.com
Whole thread Raw
Responses Re: ambiguous local variable name in 9.0 proc
List pgsql-sql
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?

pgsql-sql by date:

Previous
From: Brian Sherwood
Date:
Subject: Re: select xpath ...
Next
From: "David Johnston"
Date:
Subject: Re: ambiguous local variable name in 9.0 proc