Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EF3F5B5DC24 for ; Wed, 21 Sep 2011 20:49:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 70663-01 for ; Wed, 21 Sep 2011 23:49:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from smtp103.prem.mail.sp1.yahoo.com (smtp103.prem.mail.sp1.yahoo.com [98.136.44.58]) by mail.postgresql.org (Postfix) with SMTP id 7745CB5DC1A for ; Wed, 21 Sep 2011 20:49:15 -0300 (ADT) Received: (qmail 10324 invoked from network); 21 Sep 2011 23:49:14 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=zZB/0PnaKEuV+Ya/nfhbj9JcC6QDJ4lKSWP64J7dMGDTsNpQAi72wOLyosvzqW81hOSlQI0/u8LosVXzTHyfrvuwXjVk4g88thb7oxZCSypZM4lNumkIS+5cFE2U8W72Eh897GjTk0FbHpSCbj9NViKuIrauC6DjZ+RbbzSZZuk= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1316648954; bh=/CPDgDnXW+cte9LY3CDtr9XQ6w9reUeepneDLc4Zvqk=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=RLx0r7be6l4KhjGhQJijikprGsW+OGnw1vHzSdGrnr4TBJQeTrXnonBzpLP2R4rIlaEZzHa1Q2q9XDRS8QeNFqDgIk7KYV9aXf7gg3Rc0MIENhqnUpFgdCujt977pYsUnwgMZca5dV4iZQAWv3HynVEjYMglWJWbcxJxJa+MXeQ= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: 6ymmyhcVM1m_wVmL17Za0WyurBlaQvJnJIluPisNcDaV8gc 7BA2ItpamhfqzVD3fQ618qlDVzTnS_BhIjglAjTedNBiOuEIeVmIdSBLSjGa NakacmZRykUhfDAIyfcuxU8JWE.XH8E3WbT5bvPfYqlgItIzuIrLe1tmYLTc OL1b6i9zftbBaBRcnOM1FMjRX6HgC3OeUvxIONG9ej.hi61bS2dz9lNqo8Qf 3aMJLYurAutNf0.blXuLMvUgSOacrDIXCjAEhwE9oxI0FB39Roz95tOFsufI SZ3JeGeg2n6iWy.hN5xjbs9NWWzFtde02zURs20LP.xq1w2YPRa5HyZMIVTX T9Jlx9eeVMDxaGBB6dxEMd1o1ohs8YzzkKKVXjVY0c21xYgsQguc49FJhP9c YvU876w-- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@24.93.23.188 with login) by smtp103.prem.mail.sp1.yahoo.com with SMTP; 21 Sep 2011 16:49:14 -0700 PDT From: "David Johnston" To: "'Samuel Gendler'" , References: In-Reply-To: Subject: Re: ambiguous local variable name in 9.0 proc Date: Wed, 21 Sep 2011 19:49:05 -0400 Message-ID: <00c301cc78b9$0ca38e30$25eaaa90$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C4_01CC7897.8593C2F0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQLUgzmI7QbQofx7SaUmWgmoRhLN9ZNIaWxA Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.401 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.504, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 201109/61 X-Sequence-Number: 35861 This is a multipart message in MIME format. ------=_NextPart_000_00C4_01CC7897.8593C2F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_00C4_01CC7897.8593C2F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

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 =3D = tbl_schema

            =             AND table_name =3D = tbl_fine_part_old ) THEN

        IF EXISTS ( = SELECT status FROM reporting_mgmt.etl_status AS = e

            =          WHERE tbl_schema =3D = e.tbl_schema

            =            AND tbl_root =3D = e.tbl_root

            =            AND div_start_old =3D = e.fine_time

            =            AND coarse_timescale =3D = e.coarse_scale

            =            AND status =3D 0 ) = THEN

 

And in 9.0, it is = generating the following error:

 

ERROR:  column = reference "tbl_schema" is = ambiguous

LINE 2:           =            WHERE tbl_schema =3D = 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 =3D = e.tbl_schema

            =           AND tbl_root =3D = e.tbl_root

            =           AND div_start_old =3D = e.fine_time 

            =           AND coarse_timescale =3D = e.coarse_scale

            =           AND status =3D 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 =3D = 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 :=3D = tbl_schema;

 

… WHERE tbl_schema_copy =3D e.tbl_schema = …

 

David J.

 

 

 

------=_NextPart_000_00C4_01CC7897.8593C2F0--