Need help returning record set from a dynamic sql query

["MuraliPD@GMail" <murali.pd@xxxxxxxxx>]

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