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