Thread: Need help returning record set from a dynamic sql query

[I'm not going to even try to work out that mess to quote it]

The following works for me.  You can even do it without dynamic sql (see
fun_orderreport1).

begin;
-- dummy up some tables for self contained example
create table orders (ordersid int, initiated date, company int, event int);
create table company (companyid int, companyname text);
create table event (eventid int, company int, eventname text);

-- dummy up some data
insert into company values (1,'COMPANY');
insert into orders values (1, current_date, 1, 1);
insert into event values(1, 1, 'EVENT');

-- make the function
CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer,
pmcompanyid integer, pmeventid integer)
RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
vSql TEXT = '
SELECT
            ORDR.ORDERSID                    AS OrderID,
            ORDR.INITIATED                       AS Order_Date,
            COMP.COMPANYNAME           AS Company_Name,
            EVNT.EVENTNAME                  AS Event_Name
FROM
            ORDERS ORDR
            INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
            INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
WHERE
            ORDR.EVENT = EVNT.EVENTID ';
BEGIN
            IF $1 IS NOT NULL THEN
                        vSql = vSql ||' AND ORDR.ORDERSID = '|| $1;
            END IF;

            IF $2 IS NOT NULL THEN
                        vSql = vSql ||' AND COMP.COMPANYID = '|| $2;
            END IF;

            IF $3 IS NOT NULL THEN
                        vSql = vSql ||' AND EVNT.EVENTID = '|| $3;
            END IF;

            FOR vResult IN EXECUTE vSql
            LOOP
              RETURN NEXT vResult;
            END LOOP;

            RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table foo on commit drop as
SELECT 1,* from fun_orderreport(NULL,NULL,NULL) a (orderid int,
order_date date, company_name text, event_name text)
union
SELECT 2,* from fun_orderreport(1,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 3,* from fun_orderreport(NULL,1,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 4,* from fun_orderreport(NULL,NULL,1) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 5,* from fun_orderreport(2,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 6,* from fun_orderreport(1,1,1) a (orderid int, order_date date,
company_name text, event_name text);

-- do the same thing without execute
CREATE OR REPLACE FUNCTION fun_orderreport1(pmorderid integer,
pmcompanyid integer, pmeventid integer)
RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
begin
   for vResult in
      SELECT
            ORDR.ORDERSID                    AS OrderID,
            ORDR.INITIATED                       AS Order_Date,
            COMP.COMPANYNAME           AS Company_Name,
            EVNT.EVENTNAME                  AS Event_Name
      FROM
            ORDERS ORDR
            INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
            INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID and
evnt.eventid = ordr.event
      WHERE
      ordr.ordersid is not distinct from coalesce($1, ordr.ordersid)
      and comp.companyid is not distinct from coalesce($2, comp.companyid)
      and evnt.eventid is not distinct from coalesce($3, evnt.eventid)
   loop
      RETURN NEXT vResult;
   END LOOP;

   RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table bar on commit drop as
SELECT 1,* from fun_orderreport1(NULL,NULL,NULL) a (orderid int,
order_date date, company_name text, event_name text)
union
SELECT 2,* from fun_orderreport1(1,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 3,* from fun_orderreport1(NULL,1,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 4,* from fun_orderreport1(NULL,NULL,1) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 5,* from fun_orderreport1(2,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 6,* from fun_orderreport1(1,1,1) a (orderid int, order_date date,
company_name text, event_name text);

-- if this returns any rows there's a difference
(select * from foo except select * from bar)
union
(select * from bar except select * from foo);

Just a thought....

Why can't you create a temporary table from your dynamic query and use that temp table in the for loop.

Thnx,
Gnanavel

----- Original Message -----
From: "Sathish Duraiswamy" <sathish@leatherlink.net>
To: "MuraliPD@GMail" <murali.pd@gmail.com>
Cc: "Willy-Bas Loos" <willybas@gmail.com>, pgsql-general@postgresql.org
Sent: Wednesday, August 13, 2008 9:40:51 AM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query


Murali,

Tried the same method using FOR --LOOP with EXECUTE command similar function you described and got the same error
message. 

When i used raise info to check the function , i get the set of records as result .But finally , it throws same error

Someone can help on this issue..

Regrds
sathish




On Tue, Aug 12, 2008 at 7:26 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:






Hi,



I have changed my procedure like below,



CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid integer, pmeventid integer)

RETURNS SETOF orderreport AS

$BODY$


DECLARE

vResult ORDERREPORT%ROWTYPE;

vSql TEXT = '

SELECT

ORDR.ORDERSID AS OrderID,

ORDR.INITIATED AS Order_Date,

COMP.COMPANYNAME AS Company_Name,

EVNT.EVENTNAME AS Event_Name

FROM

ORDERS ORDR

INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY

INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID

WHERE

ORDR.EVENT = EVNT.EVENTID ';

BEGIN

IF $1 IS NOT NULL THEN

vSql = vSql ||' AND ORDR.ORDERSID = '|| $1;

END IF;



IF $2 IS NOT NULL THEN

vSql = vSql ||' AND COMP.COMPANYID = '|| $2;

END IF;



IF $3 IS NOT NULL THEN

vSql = vSql ||' AND EVNT.EVENTID = '|| $3;

END IF;

vSql = vSql || ';';

vSql = '';

-- DEALLOCATE PREPARE vSql;

FOR vResult IN EXECUTE vSql

LOOP

RETURN NEXT vResult;

END LOOP;

RETURN;

END $BODY$

LANGUAGE 'plpgsql' VOLATILE;





SELECT fun_orderreport(NULL,NULL,NULL);



But the error I get when I execute,



ERROR: cannot open multi-query plan as cursor

CONTEXT: PL/pgSQL function "fun_orderreport" line 30 at for over execute statement



********** Error **********



ERROR: cannot open multi-query plan as cursor

SQL state: 42P11

Context: PL/pgSQL function "fun_orderreport" line 30 at for over execute statement









From: MuraliPD@GMail [mailto: murali.pd@gmail.com ]
Sent: Tuesday, August 12, 2008 6:53 PM
To: 'Willy-Bas Loos'
Cc: 'Sathish Duraiswamy'; ' pgsql-general@postgresql.org '
Subject: RE: [GENERAL] Need help returning record set from a dynamic sql query




Please understand…



I know I have to use FOR … LOOP for my query. But it is not a normal one …I use to build that one dynamically.






From: Willy-Bas Loos [mailto: willybas@gmail.com ]
Sent: Tuesday, August 12, 2008 5:46 PM
To: MuraliPD@GMail
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org



Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query







>Iam getting just the first record from the recordset
That's because you use SELECT INTO, you should use FOR rec IN <query> LOOP

Here's sample code from http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);

CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM ReturnNexting('a');


On Tue, Aug 12, 2008 at 12:58 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:



Hi Sathish,



Thanks for your reply.



But I have created the type to return the record set from my join query using a stored function.



I cannot able to create a table with that details …. Since those details will be already available from different
tables. 



One more thing …. I am clear with your result set using FOR ..LOOP but mine is not a normal query… it was built Dynamic
basedon my Input Parameters.  



Please look and tell me if you are not clear with my query.





Thanks,

MuraliDharan V




From: Sathish Duraiswamy [mailto: sathish@leatherlink.net ]
Sent: Tuesday, August 12, 2008 4:10 PM
To: MuraliPD@GMail
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query






Dear murali,

We use psql for our ERP software .We found CREATE TYPE is useful in creating new data type similar to creating domain.
For eg
CREATE TYPE date_condition (
condition_id int,
from_date date,
to_date date);

Instead , you can CREATE TABLE (ORDERSID INTEGER,ORDERDATE TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);

You can have result returning a set of records

Feel free to comment on it

Regrds
sathish


On Tue, Aug 12, 2008 at 3:08 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:


Hi,

Iam new to Postgresql, now i need to create a Dynamic SQL Query for returning the record set
based on my Input Parameters. I looked up some of the documents and worked out some more ...

MY Postgresql Version In Local: 7.4
MY Postgresql Version In Development: 8.2


-- DROP TYPE ORDERREPORT;
CREATE TYPE ORDERREPORT AS (ORDERSID INTEGER,ORDERDATE TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);


-- DROP FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN pmCompanyId INTEGER,IN pmEventId INTEGER);
CREATE OR REPLACE FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN pmCompanyId INTEGER,IN pmEventId INTEGER) RETURNS
SETOFORDERREPORT AS  
'
DECLARE
vResult ORDERREPORT%ROWTYPE;
vSql TEXT = ''
SELECT
ORDR.ORDERSID AS OrderID,
ORDR.INITIATED AS Order_Date,
COMP.COMPANYNAME AS Company_Name,
EVNT.EVENTNAME AS Event_Name
FROM
ORDERS ORDR
INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
WHERE
ORDR.EVENT = EVNT.EVENTID '';
BEGIN
IF $1 IS NOT NULL THEN
vSql = vSql ||'' AND ORDR.ORDERSID = ''|| $1;
END IF;

IF $2 IS NOT NULL THEN
vSql = vSql ||'' AND COMP.COMPANYID = ''|| $2;
END IF;

IF $3 IS NOT NULL THEN
vSql = vSql ||'' AND EVNT.EVENTID = ''|| $3;
END IF;
EXECUTE vSql INTO vResult;
RETURN NEXT vResult;
END '
LANGUAGE 'PLPGSQL';

Result:

events=# SELECT * FROM FUN_ORDERREPORT(102881,NULL,NULL);
ordersid | orderdate | companyname | eventname
----------+----------------------------+-------------+-----------------
102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
(1 row)

events=# SELECT * FROM FUN_ORDERREPORT(NULL,NULL,NULL);
ordersid | orderdate | companyname | eventname
----------+----------------------------+-------------+-----------------
102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
(1 row)


Iam getting just the first record from the recordset.
Can someone help me how can i return all query results from a Dynamic SQL Query?


--
Thanks,
MuraliDharan V

Hi Sathish,

I too mentioned the same thing..... I have changed my code and checked ...but not got that worked.

Here is the code which I finally got worked !!!

CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid integer, pmeventid integer)
RETURNS SETOF orderreport AS
$BODY$
DECLARE
    vResult ORDERREPORT%ROWTYPE;
    vSql TEXT = '

SELECT
    ORDR.ORDERSID AS OrderID,
    ORDR.INITIATED AS Order_Date,
    COMP.COMPANYNAME AS Company_Name,
    EVNT.EVENTNAME AS Event_Name
FROM
    ORDERS ORDR
    INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
    INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
WHERE
    ORDR.EVENT = EVNT.EVENTID ';

BEGIN

    IF $1 IS NOT NULL THEN
        vSql = vSql ||' AND ORDR.ORDERSID = '|| $1;

    END IF;

    IF $2 IS NOT NULL THEN
        vSql = vSql ||' AND COMP.COMPANYID = '|| $2;
    END IF;

    IF $3 IS NOT NULL THEN
        vSql = vSql ||' AND EVNT.EVENTID = '|| $3;
    END IF;

    vSql = vSql || ';';

    FOR vResult IN EXECUTE vSql
    LOOP
        RETURN NEXT vResult;
    END LOOP;
    RETURN;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE;

SELECT * FROM fun_orderreport(NULL,NULL,NULL);


Thanks,
MuraliDharan V

-----Original Message-----
From: Gnanavel Shanmugam [mailto:s.gnanavel@gmail.com]
Sent: Thursday, August 14, 2008 12:11 PM
To: Sathish Duraiswamy
Cc: Willy-Bas Loos; pgsql-general@postgresql.org; MuraliPD@GMail
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query

Just a thought....

Why can't you create a temporary table from your dynamic query and use that temp table in the for loop.

Thnx,
Gnanavel

----- Original Message -----
From: "Sathish Duraiswamy" <sathish@leatherlink.net>
To: "MuraliPD@GMail" <murali.pd@gmail.com>
Cc: "Willy-Bas Loos" <willybas@gmail.com>, pgsql-general@postgresql.org
Sent: Wednesday, August 13, 2008 9:40:51 AM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query


Murali,

Tried the same method using FOR --LOOP with EXECUTE command similar function you described and got the same error
message. 

When i used raise info to check the function , i get the set of records as result .But finally , it throws same error

Someone can help on this issue..

Regrds
sathish




On Tue, Aug 12, 2008 at 7:26 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:






Hi,



I have changed my procedure like below,



CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid integer, pmeventid integer)

RETURNS SETOF orderreport AS

$BODY$


DECLARE

vResult ORDERREPORT%ROWTYPE;

vSql TEXT = '

SELECT

ORDR.ORDERSID AS OrderID,

ORDR.INITIATED AS Order_Date,

COMP.COMPANYNAME AS Company_Name,

EVNT.EVENTNAME AS Event_Name

FROM

ORDERS ORDR

INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY

INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID

WHERE

ORDR.EVENT = EVNT.EVENTID ';

BEGIN

IF $1 IS NOT NULL THEN

vSql = vSql ||' AND ORDR.ORDERSID = '|| $1;

END IF;



IF $2 IS NOT NULL THEN

vSql = vSql ||' AND COMP.COMPANYID = '|| $2;

END IF;



IF $3 IS NOT NULL THEN

vSql = vSql ||' AND EVNT.EVENTID = '|| $3;

END IF;

vSql = vSql || ';';

vSql = '';

-- DEALLOCATE PREPARE vSql;

FOR vResult IN EXECUTE vSql

LOOP

RETURN NEXT vResult;

END LOOP;

RETURN;

END $BODY$

LANGUAGE 'plpgsql' VOLATILE;





SELECT fun_orderreport(NULL,NULL,NULL);



But the error I get when I execute,



ERROR: cannot open multi-query plan as cursor

CONTEXT: PL/pgSQL function "fun_orderreport" line 30 at for over execute statement



********** Error **********



ERROR: cannot open multi-query plan as cursor

SQL state: 42P11

Context: PL/pgSQL function "fun_orderreport" line 30 at for over execute statement









From: MuraliPD@GMail [mailto: murali.pd@gmail.com ]
Sent: Tuesday, August 12, 2008 6:53 PM
To: 'Willy-Bas Loos'
Cc: 'Sathish Duraiswamy'; ' pgsql-general@postgresql.org '
Subject: RE: [GENERAL] Need help returning record set from a dynamic sql query




Please understand…



I know I have to use FOR … LOOP for my query. But it is not a normal one …I use to build that one dynamically.






From: Willy-Bas Loos [mailto: willybas@gmail.com ]
Sent: Tuesday, August 12, 2008 5:46 PM
To: MuraliPD@GMail
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org



Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query







>Iam getting just the first record from the recordset
That's because you use SELECT INTO, you should use FOR rec IN <query> LOOP

Here's sample code from http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);

CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM ReturnNexting('a');


On Tue, Aug 12, 2008 at 12:58 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:



Hi Sathish,



Thanks for your reply.



But I have created the type to return the record set from my join query using a stored function.



I cannot able to create a table with that details …. Since those details will be already available from different
tables. 



One more thing …. I am clear with your result set using FOR ..LOOP but mine is not a normal query… it was built Dynamic
basedon my Input Parameters.  



Please look and tell me if you are not clear with my query.





Thanks,

MuraliDharan V




From: Sathish Duraiswamy [mailto: sathish@leatherlink.net ]
Sent: Tuesday, August 12, 2008 4:10 PM
To: MuraliPD@GMail
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query






Dear murali,

We use psql for our ERP software .We found CREATE TYPE is useful in creating new data type similar to creating domain.
For eg
CREATE TYPE date_condition (
condition_id int,
from_date date,
to_date date);

Instead , you can CREATE TABLE (ORDERSID INTEGER,ORDERDATE TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);

You can have result returning a set of records

Feel free to comment on it

Regrds
sathish


On Tue, Aug 12, 2008 at 3:08 PM, MuraliPD@GMail < murali.pd@gmail.com > wrote:


Hi,

Iam new to Postgresql, now i need to create a Dynamic SQL Query for returning the record set
based on my Input Parameters. I looked up some of the documents and worked out some more ...

MY Postgresql Version In Local: 7.4
MY Postgresql Version In Development: 8.2


-- DROP TYPE ORDERREPORT;
CREATE TYPE ORDERREPORT AS (ORDERSID INTEGER,ORDERDATE TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);


-- DROP FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN pmCompanyId INTEGER,IN pmEventId INTEGER);
CREATE OR REPLACE FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN pmCompanyId INTEGER,IN pmEventId INTEGER) RETURNS
SETOFORDERREPORT AS  
'
DECLARE
vResult ORDERREPORT%ROWTYPE;
vSql TEXT = ''
SELECT
ORDR.ORDERSID AS OrderID,
ORDR.INITIATED AS Order_Date,
COMP.COMPANYNAME AS Company_Name,
EVNT.EVENTNAME AS Event_Name
FROM
ORDERS ORDR
INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
WHERE
ORDR.EVENT = EVNT.EVENTID '';
BEGIN
IF $1 IS NOT NULL THEN
vSql = vSql ||'' AND ORDR.ORDERSID = ''|| $1;
END IF;

IF $2 IS NOT NULL THEN
vSql = vSql ||'' AND COMP.COMPANYID = ''|| $2;
END IF;

IF $3 IS NOT NULL THEN
vSql = vSql ||'' AND EVNT.EVENTID = ''|| $3;
END IF;
EXECUTE vSql INTO vResult;
RETURN NEXT vResult;
END '
LANGUAGE 'PLPGSQL';

Result:

events=# SELECT * FROM FUN_ORDERREPORT(102881,NULL,NULL);
ordersid | orderdate | companyname | eventname
----------+----------------------------+-------------+-----------------
102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
(1 row)

events=# SELECT * FROM FUN_ORDERREPORT(NULL,NULL,NULL);
ordersid | orderdate | companyname | eventname
----------+----------------------------+-------------+-----------------
102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
(1 row)


Iam getting just the first record from the recordset.
Can someone help me how can i return all query results from a Dynamic SQL Query?


--
Thanks,
MuraliDharan V