pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPLpgSQL FOR IN EXECUTE question
5+ messages / 4 participants
[nested] [flat]
* PLpgSQL FOR IN EXECUTE question
@ 2002-11-07 14:35 Christoph Haller <ch@rodos.fzk.de>
2002-11-07 16:13 ` Re: PLpgSQL FOR IN EXECUTE question Richard Huxton <dev@archonet.com>
2002-11-07 18:01 ` Re: PLpgSQL FOR IN EXECUTE question Tom Lane <tgl@sss.pgh.pa.us>
2002-11-07 21:30 ` Re: PLpgSQL FOR IN EXECUTE question Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 5+ messages in thread
From: Christoph Haller @ 2002-11-07 14:35 UTC (permalink / raw)
To: pgsql-sql
Consider the following PLpgSQL code fragment
FOR this_record IN
EXECUTE ''SELECT ''
|| quote_ident($1)
|| ''FROM ''
|| quote_ident($2)
LOOP
list := list || '', '' || this_record.$1 ;
END LOOP;
As expected, accessing a field via this_record.$1
does not work.
Can it be done otherwise?
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PLpgSQL FOR IN EXECUTE question
2002-11-07 14:35 PLpgSQL FOR IN EXECUTE question Christoph Haller <ch@rodos.fzk.de>
@ 2002-11-07 16:13 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2002-11-07 16:13 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thursday 07 Nov 2002 2:35 pm, Christoph Haller wrote:
> Consider the following PLpgSQL code fragment
>
> FOR this_record IN
> EXECUTE ''SELECT ''
>
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
>
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
>
> As expected, accessing a field via this_record.$1
> does not work.
> Can it be done otherwise?
Perhaps "SELECT ... AS known_name FROM ..." and then this_record.known_name?
--
Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PLpgSQL FOR IN EXECUTE question
2002-11-07 14:35 PLpgSQL FOR IN EXECUTE question Christoph Haller <ch@rodos.fzk.de>
@ 2002-11-07 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-11-07 18:01 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
Christoph Haller <ch@rodos.fzk.de> writes:
> Consider the following PLpgSQL code fragment
> FOR this_record IN
> EXECUTE ''SELECT ''
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
> As expected, accessing a field via this_record.$1
> does not work.
> Can it be done otherwise?
FOR this_record IN
EXECUTE ''SELECT ''
|| quote_ident($1)
|| '' AS foo FROM ''
|| quote_ident($2)
LOOP
list := list || '', '' || this_record.foo ;
END LOOP;
There is still another gotcha here though: the datatype of foo had
better remain the same every time, else the cached query plan for
the concatenation will fail. Explicitly casting to text in the
EXECUTE'd SELECT might be a good idea:
EXECUTE ''SELECT CAST(''
|| quote_ident($1)
|| '' AS TEXT) AS foo FROM ''
|| quote_ident($2)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PLpgSQL FOR IN EXECUTE question
2002-11-07 14:35 PLpgSQL FOR IN EXECUTE question Christoph Haller <ch@rodos.fzk.de>
@ 2002-11-07 21:30 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-11-07 21:30 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
Chris,
> FOR this_record IN
> EXECUTE ''SELECT ''
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
>
> As expected, accessing a field via this_record.$1
> does not work.
> Can it be done otherwise?
Yes. Alias the columns:
FOR this_record IN
EXECUTE ''SELECT ''
|| quote_ident($1)
|| '' AS col1 FROM ''
|| quote_ident($2)
|| '' AS col2'' LOOP
list := list || '', '' || this_record.col1 ;
END LOOP;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PLpgSQL FOR IN EXECUTE question
@ 2002-11-08 09:06 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2002-11-08 09:06 UTC (permalink / raw)
To: pgsql-sql
Thanks to Josh, Richard, Tom
EXECUTE ''SELECT CAST(''
|| quote_ident($1)
|| '' AS TEXT) AS foo FROM ''
|| quote_ident($2)
and then
list := list || '', '' || this_record.foo ;
works perfectly.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-11-08 09:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-07 14:35 PLpgSQL FOR IN EXECUTE question Christoph Haller <ch@rodos.fzk.de>
2002-11-07 16:13 ` Richard Huxton <dev@archonet.com>
2002-11-07 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-07 21:30 ` Josh Berkus <josh@agliodbs.com>
2002-11-08 09:06 Re: PLpgSQL FOR IN EXECUTE question Christoph Haller <ch@rodos.fzk.de>
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