subject:"\[GENERAL\] Need help returning record set from a dynamic sql query"

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-14 Thread Gnanavel Shanmugam

Just a thought

Why can't you create a temporary table from your dynamic query and use that 
temp table in the for loop.

Thnx,
Gnanavel

- Original Message -
From: Sathish Duraiswamy [EMAIL PROTECTED]
To: [EMAIL PROTECTED] [EMAIL PROTECTED]
Cc: Willy-Bas Loos [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Wednesday, August 13, 2008 9:40:51 AM GMT +05:30 Chennai, Kolkata, 
Mumbai, New Delhi
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query


Murali, 

Tried the same method using FOR --LOOP with EXECUTE command similar function 
you described and got the same error message. 

When i used raise info to check the function , i get the set of records as 
result .But finally , it throws same error 

Someone can help on this issue.. 

Regrds 
sathish 




On Tue, Aug 12, 2008 at 7:26 PM, [EMAIL PROTECTED]  [EMAIL PROTECTED]  wrote: 






Hi, 



I have changed my procedure like below, 



CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid 
integer, pmeventid integer) 

RETURNS SETOF orderreport AS 

$BODY$ 


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; 

vSql = vSql || ';'; 

vSql = ''; 

-- DEALLOCATE PREPARE vSql; 

FOR vResult IN EXECUTE vSql 

LOOP 

RETURN NEXT vResult; 

END LOOP; 

RETURN; 

END $BODY$ 

LANGUAGE 'plpgsql' VOLATILE; 





SELECT fun_orderreport(NULL,NULL,NULL); 



But the error I get when I execute, 



ERROR: cannot open multi-query plan as cursor 

CONTEXT: PL/pgSQL function fun_orderreport line 30 at for over execute 
statement 



** Error ** 



ERROR: cannot open multi-query plan as cursor 

SQL state: 42P11 

Context: PL/pgSQL function fun_orderreport line 30 at for over execute 
statement 









From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] ] 
Sent: Tuesday, August 12, 2008 6:53 PM 
To: 'Willy-Bas Loos' 
Cc: 'Sathish Duraiswamy'; ' pgsql-general@postgresql.org ' 
Subject: RE: [GENERAL] Need help returning record set from a dynamic sql query 




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: [EMAIL PROTECTED] ] 
Sent: Tuesday, August 12, 2008 5:46 PM 
To: [EMAIL PROTECTED] 
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org 



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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-14 Thread [EMAIL PROTECTED]

Hi Sathish,

I too mentioned the same thing. I have changed my code and checked ...but 
not got that worked.

Here is the code which I finally got worked !!!

CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid 
integer, pmeventid integer) 
RETURNS SETOF orderreport AS 
$BODY$ 
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; 

vSql = vSql || ';'; 

FOR vResult IN EXECUTE vSql 
LOOP 
RETURN NEXT vResult; 
END LOOP; 
RETURN; 
END $BODY$ 
LANGUAGE 'plpgsql' VOLATILE; 

SELECT * FROM fun_orderreport(NULL,NULL,NULL);


Thanks,
MuraliDharan V

-Original Message-
From: Gnanavel Shanmugam [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 14, 2008 12:11 PM
To: Sathish Duraiswamy
Cc: Willy-Bas Loos; pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query

Just a thought

Why can't you create a temporary table from your dynamic query and use that 
temp table in the for loop.

Thnx,
Gnanavel

- Original Message -
From: Sathish Duraiswamy [EMAIL PROTECTED]
To: [EMAIL PROTECTED] [EMAIL PROTECTED]
Cc: Willy-Bas Loos [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Wednesday, August 13, 2008 9:40:51 AM GMT +05:30 Chennai, Kolkata, 
Mumbai, New Delhi
Subject: Re: [GENERAL] Need help returning record set from a dynamic sql query


Murali, 

Tried the same method using FOR --LOOP with EXECUTE command similar function 
you described and got the same error message. 

When i used raise info to check the function , i get the set of records as 
result .But finally , it throws same error 

Someone can help on this issue.. 

Regrds 
sathish 




On Tue, Aug 12, 2008 at 7:26 PM, [EMAIL PROTECTED]  [EMAIL PROTECTED]  wrote: 






Hi, 



I have changed my procedure like below, 



CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid 
integer, pmeventid integer) 

RETURNS SETOF orderreport AS 

$BODY$ 


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; 

vSql = vSql || ';'; 

vSql = ''; 

-- DEALLOCATE PREPARE vSql; 

FOR vResult IN EXECUTE vSql 

LOOP 

RETURN NEXT vResult; 

END LOOP; 

RETURN; 

END $BODY$ 

LANGUAGE 'plpgsql' VOLATILE; 





SELECT fun_orderreport(NULL,NULL,NULL); 



But the error I get when I execute, 



ERROR: cannot open multi-query plan as cursor 

CONTEXT: PL/pgSQL function fun_orderreport line 30 at for over execute 
statement 



** Error ** 



ERROR: cannot open multi-query plan as cursor 

SQL state: 42P11 

Context: PL/pgSQL function fun_orderreport line 30 at for over execute 
statement 









From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] ] 
Sent: Tuesday, August 12, 2008 6:53 PM 
To: 'Willy-Bas Loos' 
Cc: 'Sathish Duraiswamy'; ' pgsql-general@postgresql.org ' 
Subject: RE: [GENERAL] Need help returning record set from a dynamic sql query 




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: [EMAIL PROTECTED] ] 
Sent: Tuesday, August 12, 2008 5:46 PM 
To: [EMAIL PROTECTED] 
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org 



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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-13 Thread Klint Gore


[I'm not going to even try to work out that mess to quote it]

The following works for me.  You can even do it without dynamic sql (see 
fun_orderreport1).


begin;
-- dummy up some tables for self contained example
create table orders (ordersid int, initiated date, company int, event int);
create table company (companyid int, companyname text);
create table event (eventid int, company int, eventname text);

-- dummy up some data
insert into company values (1,'COMPANY');
insert into orders values (1, current_date, 1, 1);
insert into event values(1, 1, 'EVENT');

-- make the function
CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, 
pmcompanyid integer, pmeventid integer)

RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
vSql TEXT = '
SELECT
   ORDR.ORDERSIDAS 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;

   FOR vResult IN EXECUTE vSql
   LOOP
 RETURN NEXT vResult;
   END LOOP;

   RETURN; 
END;

$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table foo on commit drop as
SELECT 1,* from fun_orderreport(NULL,NULL,NULL) a (orderid int, 
order_date date, company_name text, event_name text)

union
SELECT 2,* from fun_orderreport(1,NULL,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 3,* from fun_orderreport(NULL,1,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 4,* from fun_orderreport(NULL,NULL,1) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 5,* from fun_orderreport(2,NULL,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 6,* from fun_orderreport(1,1,1) a (orderid int, order_date date, 
company_name text, event_name text);


-- do the same thing without execute
CREATE OR REPLACE FUNCTION fun_orderreport1(pmorderid integer, 
pmcompanyid integer, pmeventid integer)

RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
begin
  for vResult in
 SELECT
   ORDR.ORDERSIDAS 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 and 
evnt.eventid = ordr.event

 WHERE
 ordr.ordersid is not distinct from coalesce($1, ordr.ordersid)
 and comp.companyid is not distinct from coalesce($2, comp.companyid)
 and evnt.eventid is not distinct from coalesce($3, evnt.eventid)
  loop
 RETURN NEXT vResult;
  END LOOP;

  RETURN; 
END;

$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table bar on commit drop as
SELECT 1,* from fun_orderreport1(NULL,NULL,NULL) a (orderid int, 
order_date date, company_name text, event_name text)

union
SELECT 2,* from fun_orderreport1(1,NULL,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 3,* from fun_orderreport1(NULL,1,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 4,* from fun_orderreport1(NULL,NULL,1) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 5,* from fun_orderreport1(2,NULL,NULL) a (orderid int, order_date 
date, company_name text, event_name text)

union
SELECT 6,* from fun_orderreport1(1,1,1) a (orderid int, order_date date, 
company_name text, event_name text);


-- if this returns any rows there's a difference
(select * from foo except select * from bar)
union
(select * from bar except select * from foo);

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread [EMAIL PROTECTED]

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread Sathish Duraiswamy

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_datedate);

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread [EMAIL PROTECTED]

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_datedate);

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread Willy-Bas Loos

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_datedate);

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread [EMAIL PROTECTED]

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:[EMAIL PROTECTED] 
Sent: Tuesday, August 12, 2008 5:46 PM
To: [EMAIL PROTECTED]
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org
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.ht
ml

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_datedate);

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread [EMAIL PROTECTED]

Hi,

 

I have changed my procedure like below,

 

CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid
integer, pmeventid integer)

  RETURNS SETOF orderreport AS

$BODY$

DECLARE 

vResult ORDERREPORT%ROWTYPE;

vSql TEXT = '

SELECT

ORDR.ORDERSIDAS 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;

vSql = vSql || ';';

vSql = '';

-- DEALLOCATE PREPARE vSql;

FOR vResult IN EXECUTE vSql

LOOP

  RETURN NEXT vResult;

END LOOP;

RETURN;  

END $BODY$

  LANGUAGE 'plpgsql' VOLATILE;

 

 

SELECT fun_orderreport(NULL,NULL,NULL);

 

But the error I get when I execute,

 

ERROR:  cannot open multi-query plan as cursor

CONTEXT:  PL/pgSQL function fun_orderreport line 30 at for over execute
statement

 

** Error **

 

ERROR: cannot open multi-query plan as cursor

SQL state: 42P11

Context: PL/pgSQL function fun_orderreport line 30 at for over execute
statement

 

 

 

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 12, 2008 6:53 PM
To: 'Willy-Bas Loos'
Cc: 'Sathish Duraiswamy'; 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] Need help returning record set from a dynamic sql
query

 

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:[EMAIL PROTECTED] 
Sent: Tuesday, August 12, 2008 5:46 PM
To: [EMAIL PROTECTED]
Cc: Sathish Duraiswamy; pgsql-general@postgresql.org
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.ht
ml

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_datedate);

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

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread Willy-Bas Loos

so use EXECUTE:


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;
vQuery text := 'SELECT * FROM test WHERE textcol = '''||pText||;
BEGIN
FOR rec IN EXECUTE vQuery LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM ReturnNexting('a');

hth
WBL

Re: [GENERAL] Need help returning record set from a dynamic sql query

2008-08-12 Thread Sathish Duraiswamy

Murali,

Tried the same method using FOR --LOOP with EXECUTE command similar function
you described and got the same error message.

When i used raise info to check the function , i get the set of records as
result .But finally , it throws same error

Someone can help on this issue..

Regrds
sathish



On Tue, Aug 12, 2008 at 7:26 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

  Hi,



 I have changed my procedure like below,



 CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid
 integer, pmeventid integer)

   RETURNS SETOF orderreport AS

 $BODY$

 DECLARE

 vResult ORDERREPORT%ROWTYPE;

 vSql TEXT = '

 SELECT

 ORDR.ORDERSIDAS 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;

 vSql = vSql || ';';

 vSql = '';

 -- DEALLOCATE PREPARE vSql;

 FOR vResult IN EXECUTE vSql

 LOOP

   RETURN NEXT vResult;

 END LOOP;

 RETURN;

 END $BODY$

   LANGUAGE 'plpgsql' VOLATILE;





 SELECT fun_orderreport(NULL,NULL,NULL);



 But the error I get when I execute,



 ERROR:  cannot open multi-query plan as cursor

 CONTEXT:  PL/pgSQL function fun_orderreport line 30 at for over execute
 statement



 ** Error **



 ERROR: cannot open multi-query plan as cursor

 SQL state: 42P11

 Context: PL/pgSQL function fun_orderreport line 30 at for over execute
 statement







 *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 *Sent:* Tuesday, August 12, 2008 6:53 PM
 *To:* 'Willy-Bas Loos'
 *Cc:* 'Sathish Duraiswamy'; 'pgsql-general@postgresql.org'
 *Subject:* RE: [GENERAL] Need help returning record set from a dynamic sql
 query



 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:[EMAIL PROTECTED]
 *Sent:* Tuesday, August 12, 2008 5:46 PM
 *To:* [EMAIL PROTECTED]
 *Cc:* Sathish Duraiswamy; pgsql-general@postgresql.org

 *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, [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_datedate);

 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

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

[GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

Re: [GENERAL] Need help returning record set from a dynamic sql query

11 matches

Site Navigation

Mail list logo

Footer information