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