[ycjack]

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

[rski]

Originally Posted by ycjack

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.  

[ycjack]

Hi, rski

Thank you so much! It does work.


Jack