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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > *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:[EMAIL PROTECTED] > *Sent:* Tuesday, August 12, 2008 5:46 PM > *To:* [EMAIL PROTECTED] > *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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] > *Sent:* Tuesday, August 12, 2008 4:10 PM > *To:* [EMAIL PROTECTED] > *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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> > 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 > > > > >
