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