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,

 

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