From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: PLpgSQL FOR IN EXECUTE question
Date: Thu, 07 Nov 2002 13:01:31 -0500
Message-ID: <23283.1036692091@sss.pgh.pa.us> (raw)
In-Reply-To: <3DCA7A2E.6DD4CABE@rodos.fzk.de>
References: <3DCA7A2E.6DD4CABE@rodos.fzk.de>
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
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, ch@rodos.fzk.de
Subject: Re: PLpgSQL FOR IN EXECUTE question
In-Reply-To: <23283.1036692091@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