Re: 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(at)GMail <murali(dot)pd(at)gmail(dot)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(at)GMail [mailto:murali(dot)pd(at)gmail(dot)com]
> *Sent:* Tuesday, August 12, 2008 6:53 PM
> *To:* 'Willy-Bas Loos'
> *Cc:* 'Sathish Duraiswamy'; 'pgsql-general(at)postgresql(dot)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(at)gmail(dot)com]
> *Sent:* Tuesday, August 12, 2008 5:46 PM
> *To:* MuraliPD(at)GMail
> *Cc:* Sathish Duraiswamy; pgsql-general(at)postgresql(dot)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(at)GMail <murali(dot)pd(at)gmail(dot)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 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(at)leatherlink(dot)net]
> *Sent:* Tuesday, August 12, 2008 4:10 PM
> *To:* MuraliPD(at)GMail
> *Cc:* pgsql-general(at)postgresql(dot)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(at)GMail <murali(dot)pd(at)gmail(dot)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 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
>
>
>
>
>