Re: Getting results from a dynamic query in PL/pgSQL

Alain RICHARD wrote:

> Look at chapter 19.6.4 Looping through query results.
>
> i.e using plpgsql language:
> DECLARE
> myRecord RECORD
> BEGIN
> ...
> FOR myRecord IN EXECUTE ''SELECT col1, col2 FROM myTable '' LOOP
> -- statements using myRecord.col1 and myRecord.col2 ;
> END LOOP;
> ...
> END;
> FOR

Thank You Alain and Tom for Your replies.
That function works well when the name of the column is known. However,
I do not always know the column name.

As I have read in another post from Tom Lane that there is no support
for dynamic column names in PL/pgSQL (correct me if I'm wrong) - I tried
to implement my trigger functions in PL/Tcl.

PL/Tcl allows to use dynamic column names, but I could not figure out
how to pass strings to a SQL query in PL/Tcl:

Given the following table:

test=# select * from z;
u | v | w
---+---+---
a | x | y
b | z | z
(2 rows)

and this function:

CREATE OR REPLACE FUNCTION pgtest(VARCHAR) RETURNS VARCHAR AS '
spi_exec "SELECT u from z where v = ''[quote $1]''"
return $u
' LANGUAGE 'pltcl';

I get the following results:

test=# select pgtest('x');
ERROR: Attribute 'x' not found
test=# select pgtest('w');
pgtest
--------
b
(1 row)

This indicates that Postgres uses the parameter as column name.
I tried some ways of quoting the parameter, but it is always used
as column name.
How do I pass the value so that it is used as a string literal in
the query. (making pgtest('x') return the value 'a')

Thank You in advance,

Hans