Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 11C2DB5DC64 for ; Wed, 21 Sep 2011 20:55:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 43919-01-2 for ; Wed, 21 Sep 2011 23:55:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-pz0-f44.google.com (mail-pz0-f44.google.com [209.85.210.44]) by mail.postgresql.org (Postfix) with ESMTP id EF506B5DC61 for ; Wed, 21 Sep 2011 20:55:28 -0300 (ADT) Received: by pzk32 with SMTP id 32so5829949pzk.3 for ; Wed, 21 Sep 2011 16:55:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.19.6 with SMTP id a6mr344876pbe.46.1316649328256; Wed, 21 Sep 2011 16:55:28 -0700 (PDT) Received: by 10.68.58.134 with HTTP; Wed, 21 Sep 2011 16:55:28 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: <00c301cc78b9$0ca38e30$25eaaa90$@yahoo.com> References: <00c301cc78b9$0ca38e30$25eaaa90$@yahoo.com> Date: Wed, 21 Sep 2011 16:55:28 -0700 Message-ID: Subject: Re: ambiguous local variable name in 9.0 proc From: Samuel Gendler To: David Johnston Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=bcaec5215e678126d304ad7c4fb7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/62 X-Sequence-Number: 35862 --bcaec5215e678126d304ad7c4fb7 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Sep 21, 2011 at 4:49 PM, David Johnston wrote: > ** ** > > * > * > > 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** > ** > > ** > Yep. It is also possible to change the db behaviour globally or on a per-function basis from http://developer.postgresql.org/pgdocs/postgres/plpgsql-implementation.html "To change this behavior on a system-wide basis, set the configuration parameter plpgsql.variable_conflict to one of error, use_variable, or use_column(where error is the factory default)." 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 #variable_conflict use_column Sorry for jumping straight a mailing list query. It was actually relatively easy to find in the docs. Now to figure out if the function name scoping trick works in 8.4 so that I can modify my procs prior to upgrading my db. --sam --bcaec5215e678126d304ad7c4fb7 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Sep 21, 2011 at 4:49 PM, David J= ohnston <polobo@ya= hoo.com> wrote:

=A0=

<= p class=3D"MsoNormal"> Not tested but I think all l= ocal variables are implicitly scoped to the function name so you should be = able to do the following:
=A0

WHERE= reporting_mgmt.aggregate_timescales_impl.tbl_schema =3D e.tbl_schem= a
=A0
<= /blockquote>

Yep. =A0It is also possible to change the db behaviour = globally or on a per-function basis

from http://developer.postgresql.org/pgdocs/postgres/plpgsql-implementatio= n.html

"To change this behavior on = a system-wide basis, set the configuration parameter=A0plpgsql.variable_conflict=A0to one of=A0error<= /tt>,=A0use_variable, or=A0use_column(where=A0error=A0is the factory = default)."
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_confli= ct error #variable_conflict use_variable=A0#variable_conflict use_column =A0 Sorry for jumping straight a mailing list query. =A0It was actually re= latively easy to find in the docs. =A0Now to figure out if the function nam= e scoping trick works in 8.4 so that I can modify my procs prior to upgradi= ng my db. --sam --bcaec5215e678126d304ad7c4fb7--