By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,943 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,943 IT Pros & Developers. It's quick & easy.

a problem of function return setof

P: 4
Hi, all
Following is a simple function which returns type of setof:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION tesp_report_query()
  2.   RETURNS SETOF rpt.tesp_report_query AS
  3. $BODY$
  4. DECLARE
  5.     rec record;
  6. BEGIN
  7.     create temp table tmpt_1 on commit drop as 
  8.         select * from test_item_info;
  9.  
  10.     FOR rec IN 
  11.     select * from tmpt_1 LOOP
  12.         RETURN NEXT rec;
  13.     END LOOP;
  14.  
  15. RETURN;
  16.  
  17. END;$BODY$
  18.   LANGUAGE 'plpgsql';
-------
It works fine except that it only works once in a connection session. To make it work again have to restart a new connection. Is there any way to fix it? Thank you very much!

Jack
May 8 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
Hi, all
Following is a simple function which returns type of setof:

CREATE OR REPLACE FUNCTION tesp_report_query()
RETURNS SETOF rpt.tesp_report_query AS
$BODY$
DECLARE
rec record;
BEGIN
create temp table tmpt_1 on commit drop as
select * from test_item_info;

FOR rec IN
select * from tmpt_1 LOOP
RETURN NEXT rec;
END LOOP;

RETURN;

END;$BODY$
LANGUAGE 'plpgsql';
-------
It works fine except that it only works once in a connection session. To make it work again have to restart a new connection. Is there any way to fix it? Thank you very much!

Jack
You have to use dynamic queries, check if this works for you
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION tesp_report_query()
  2.   RETURNS SETOF rpt.tesp_report_query AS
  3. $BODY$
  4. DECLARE
  5.     rec record;
  6. BEGIN
  7.     execute 'create temp table tmpt_1 on commit drop as 
  8.         select * from test_item_info';
  9.  
  10.     FOR rec IN  execute 'select * from tmpt_1' LOOP
  11.         RETURN NEXT rec;
  12.     END LOOP;
  13.  
  14. RETURN;
  15.  
  16. END;$BODY$
  17.   LANGUAGE 'plpgsql';
  18.  
May 8 '08 #2

P: 4
Hi, rski

Thank you so much! It does work.


Jack
May 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.