pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunique names in variables and columns in plsql functions
6+ messages / 3 participants
[nested] [flat]
* unique names in variables and columns in plsql functions
@ 2006-03-27 14:33 Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 14:48 ` Re: unique names in variables and columns in plsql functions Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 15:02 ` Re: unique names in variables and columns in plsql functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Wiebe Cazemier @ 2006-03-27 14:33 UTC (permalink / raw)
To: pgsql-sql
Hi,
In a plpgsl function, consider the following excerpt:
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). When I change the
variable name to anything other than "provider_id", it works OK.
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?
Attachments:
signature.asc (application/pgp-signature, 257B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique names in variables and columns in plsql functions
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
@ 2006-03-27 14:48 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 21:38 ` Re: unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-03-27 14:48 UTC (permalink / raw)
To: Wiebe Cazemier <halfgaar@gmail.com>; +Cc: pgsql-sql
On Mon, Mar 27, 2006 at 04:33:55PM +0200, Wiebe Cazemier wrote:
> Hi,
>
> In a plpgsl function, consider the following excerpt:
>
> 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). When I change the
> variable name to anything other than "provider_id", it works OK.
>
> 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?
Sadly, overloading variable names between plpgsql and SQL is *highly*
problematic. Because of this I *always* prefix plpgsql variables with
something, such as p_ for parameters and v_ for general variables.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique names in variables and columns in plsql functions
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 14:48 ` Re: unique names in variables and columns in plsql functions Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-27 21:38 ` Wiebe Cazemier <halfgaar@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Wiebe Cazemier @ 2006-03-27 21:38 UTC (permalink / raw)
To: pgsql-sql
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 16:48, Jim C. Nasby wrote:
>
>Sadly, overloading variable names between plpgsql and SQL is *highly*
>problematic. Because of this I *always* prefix plpgsql variables with
>something, such as p_ for parameters and v_ for general variables.
>
>
Hmm. Well, at least now I'm aware of it. Perhaps I will do something
similair to prefixes from now on, it would seem to be good practice.
Attachments:
signature.asc (application/pgp-signature, 257B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique names in variables and columns in plsql functions
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
@ 2006-03-27 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 15:05 ` Re: unique names in variables and columns in plsql functions Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 21:39 ` Re: unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2006-03-27 15:02 UTC (permalink / raw)
To: Wiebe Cazemier <halfgaar@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique names in variables and columns in plsql functions
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 15:02 ` Re: unique names in variables and columns in plsql functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-27 15:05 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jim C. Nasby @ 2006-03-27 15:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Wiebe Cazemier <halfgaar@gmail.com>; pgsql-sql
On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote:
> 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.
BTW, I believe SELECT investment_products.provider_id would work here,
but I'm too lazy to test that theory out.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique names in variables and columns in plsql functions
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 15:02 ` Re: unique names in variables and columns in plsql functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-27 21:39 ` Wiebe Cazemier <halfgaar@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Wiebe Cazemier @ 2006-03-27 21:39 UTC (permalink / raw)
To: pgsql-sql
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 17:02, Tom Lane wrote:
>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).
>
>
That's why I suspected it would be NULL, since provider_id wasn't
initialised yet.
>
>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.
>
I guess I'm looking at it too much as a human. I said "SELECT FROM" so,
it guess I assumed it would be clear enough which one it had to use.
But, now that I'm thinking about it some more, I agree. However, a fatal
error would also have been welcome.
Attachments:
signature.asc (application/pgp-signature, 259B) download
signature.asc (application/pgp-signature, 257B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-27 21:39 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-27 14:33 unique names in variables and columns in plsql functions Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 14:48 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 21:38 ` Wiebe Cazemier <halfgaar@gmail.com>
2006-03-27 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 15:05 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 21:39 ` Wiebe Cazemier <halfgaar@gmail.com>
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