Обсуждение: PLpgSQL FOR IN EXECUTE question

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

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

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

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;

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