Re: PLpgSQL FOR IN EXECUTE question - Mailing list 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