Skip site navigation (1) Skip section navigation (2)

Re: BUG #2451: Short column names return no values within function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Alex Weslowski" <aweslowski(at)rpa(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2451: Short column names return no values within function
Date: 2006-05-23 15:50:17
Message-ID: 14843.1148399417@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Alex Weslowski" <aweslowski(at)rpa(dot)com> writes:
> DECLARE
> 	peg VARCHAR(5) := '';
> 	rs03 VARCHAR(3) := '';
> 	rs12 VARCHAR(3) := '';
> 	rec RECORD;
> BEGIN
> 	FOR rec IN SELECT Symbol, RS03, RS12, Peg
> 	FROM TestBug WHERE Date=d
> 	ORDER BY RS12 DESC LOOP

The problem is that you've got local variables shadowing the field names
you want to use.  What the SQL engine sees from that is

	SELECT Symbol, $1, $2, $3
	FROM TestBug WHERE Date=$4
	ORDER BY $5 DESC 

and it just assigns random ?columnN? names to the record columns (which
are going to have useless empty-string values anyway).

Use different local variable names, or qualify the field references in
the SELECT, eg TestBug.RS03.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Michael FuhrDate: 2006-05-23 15:51:18
Subject: Re: BUG #2451: Short column names return no values within function
Previous:From: Chris SmithDate: 2006-05-23 06:58:03
Subject: Re: [BUGS] BUG #2444: XA Connections given by posgres driver has

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group