Re: 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
>