From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wiebe Cazemier <halfgaar@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique names in variables and columns in plsql functions
Date: Mon, 27 Mar 2006 10:02:24 -0500
Message-ID: <14263.1143471744@sss.pgh.pa.us> (raw)
In-Reply-To: <4427F7D3.3000103@gmail.com>
References: <4427F7D3.3000103@gmail.com>
Wiebe Cazemier <halfgaar@gmail.com> writes:
> DECLARE
> provider_id INTEGER;
> BEGIN
> provider_id := (SELECT provider_id FROM investment_products WHERE id =
> my_new.investment_product_id);
> END;
> After a lot of trouble, I found out this line doesn't work correctly
> with the variable name as it is. It doesn't give an error or anything,
> it just retrieves some wrong value (probably NULL).
It'll retrieve whatever the current value of the plpgsql variable
provider_id is. plpgsql always assumes that ambiguous names refer
to its variables (indeed, it isn't even directly aware that there's
any possible ambiguity here).
> I was somewhat surprised to discover this. Can't Postgres determine that
> the provider_id in the SELECT statement is not the same one as the variable?
How and why would it determine that? In general it's perfectly normal
to use plpgsql variable values in SQL commands. I don't think it'd make
the system more usable if the parser tried to apply a heuristic rule
about some occurrences being meant as variable references and other ones
not. If the rule ever got it wrong, it'd be even more confusing.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, halfgaar@gmail.com
Subject: Re: unique names in variables and columns in plsql functions
In-Reply-To: <14263.1143471744@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox