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
