Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 172E7476DF9
	for <pgsql-sql@postgresql.org>; Thu,  7 Nov 2002 13:04:39 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 80EAD4772C9
	for <pgsql-sql@postgresql.org>; Thu,  7 Nov 2002 13:01:32 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id gA7I1VhR023284;
	Thu, 7 Nov 2002 13:01:32 -0500 (EST)
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: PLpgSQL FOR IN EXECUTE question 
In-reply-to: <3DCA7A2E.6DD4CABE@rodos.fzk.de> 
References: <3DCA7A2E.6DD4CABE@rodos.fzk.de>
Comments: In-reply-to Christoph Haller <ch@rodos.fzk.de>
	message dated "Thu, 07 Nov 2002 15:35:27 +0100"
Date: Thu, 07 Nov 2002 13:01:31 -0500
Message-ID: <23283.1036692091@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/115
X-Sequence-Number: 10257

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