Thread: 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