>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, [EMAIL PROTECTED] <[EMAIL PROTECTED]>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:[EMAIL PROTECTED] > *Sent:* Tuesday, August 12, 2008 4:10 PM > *To:* [EMAIL PROTECTED] > *Cc:* pgsql-general@postgresql.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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> > 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 > > >
