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

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

Alain RICHARD
Département Etudes et Projets - Bureautique Collective
CIPAM - Site de Montpellier




                       
                      johann.uhrmann@xpecto.com
                       
                      (Johann Uhrmann)                       Pour :   pgsql-general@postgresql.org
                       
                      Envoyé par :                           cc :
                       
                      pgsql-general-owner+M36431@pos         Objet :  [GENERAL] Getting results from a dynamic query in
PL/pgSQL               
                      tgresql.org
                       

                       

                       
                      27/01/2003 16:01
                       

                       

                       



Hello,

is there a possibility to retrieve results from a dynamically generated
query in PL/pgSQL?

That would be like this:

create function foo(text) returns text as '
DECLARE
   colname ALIAS FOR $1;
   result text;
BEGIN
   SELECT INTO result colname from my_table;
   RETURN result;
END;
' language 'plpgsql';


The actual column that is to be read from my_table should be passed as
argument to the function.
The example does not read the variable colname but tries to read a
column named "colname" from my_table which is not how it should work.

The keyword "EXECUTE" which could execute dynamic queries cannot return any
value that a select statement would. (At least there is nothing about that
in the docs.)


In order to write a trigger function that reads column names out of a table
and uses those column names afterwards, I need such a functionality.

I tried a workaround by using EXECUTE to create a function that reads
only the columns I need and then calling this dynamically generated
function.
However, that works only one time - then I get the following message:

ERROR:  plpgsql: cache lookup for proc 52118 failed

plpgsql seems to cache the dynamically generated function even after it
gets
dropped or overwritten with "create or replace function...".


Does anyone know how to use column names stored in variables within
PL/pgSQL?



Thank You in advance,


Johann Uhrmann

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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