Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx] 
Sent: Thursday, August 14, 2008 12:11 PM
To: Sathish Duraiswamy
Cc: Willy-Bas Loos; pgsql-general@xxxxxxxxxxxxxx; 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@xxxxxxxxxxxxxxx>
To: "MuraliPD@GMail" <murali.pd@xxxxxxxxx>
Cc: "Willy-Bas Loos" <willybas@xxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxx > 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@xxxxxxxxx ] 
Sent: Tuesday, August 12, 2008 6:53 PM 
To: 'Willy-Bas Loos' 
Cc: 'Sathish Duraiswamy'; ' pgsql-general@xxxxxxxxxxxxxx ' 
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@xxxxxxxxx ] 
Sent: Tuesday, August 12, 2008 5:46 PM 
To: MuraliPD@GMail 
Cc: Sathish Duraiswamy; pgsql-general@xxxxxxxxxxxxxx 



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@xxxxxxxxx > 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 based on my Input Parameters. 



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





Thanks, 

MuraliDharan V 




From: Sathish Duraiswamy [mailto: sathish@xxxxxxxxxxxxxxx ] 
Sent: Tuesday, August 12, 2008 4:10 PM 
To: MuraliPD@GMail 
Cc: pgsql-general@xxxxxxxxxxxxxx 
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@xxxxxxxxx > 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 SETOF ORDERREPORT 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 






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux