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

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

johann.uhrmann@xpecto.com (Johann Uhrmann) writes:
> The keyword "EXECUTE" which could execute dynamic queries cannot return any
> value that a select statement would.

See FOR ... EXECUTE.

            regards, tom lane

I'm not sure I really understood what you want...

Here is perhaps what you need...


CREATE TABLE t1
(
   col1 text
);

create or replace function foo(text) returns text as '
DECLARE
   colname ALIAS FOR $1;
   sqlquery text;
   result text;
BEGIN
   sqlquery := ''INSERT INTO t1 (col1 ) SELECT '' || colname || '' from
toto'';
   EXECUTE( sqlquery );

   SELECT INTO result col1 FROM t1;
   DELETE FROM t1;
   RETURN result;
END;
' language 'plpgsql';


----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Johann Uhrmann
Sent: Monday, January 27, 2003 4:02 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Getting results from a dynamic query in PL/pgSQL


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

Patrick Fiche wrote:

> I'm not sure I really understood what you want...
>
> Here is perhaps what you need...
>
>
> CREATE TABLE t1
> (
>    col1 text
> );
>
> create or replace function foo(text) returns text as '
> DECLARE
>    colname ALIAS FOR $1;
>    sqlquery text;
>    result text;
> BEGIN
>    sqlquery := ''INSERT INTO t1 (col1 ) SELECT '' || colname || '' from
> toto'';
>    EXECUTE( sqlquery );
>
>    SELECT INTO result col1 FROM t1;
>    DELETE FROM t1;
>    RETURN result;
> END;
> ' language 'plpgsql';


Thank You Patrick. I consider Your solution as a workaround as it
uses a temporary table.

As far as I know, PL/Tcl directly supports dynamic table names - but
now I have difficulties to specify a literal value which should not
be treated as column name but as varchar-value.

Please see my other post for details.

Regards,

Hans