a problem of function return setof

[ycjack]

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

[CODE=SQL]CREATE OR REPLACE FUNCTION tesp_report_que ry()
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';[/CODE]
-------
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]

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

CREATE OR REPLACE FUNCTION tesp_report_que ry()
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

CREATE OR REPLACE FUNCTION tesp_report_query()
  RETURNS SETOF rpt.tesp_report_query AS
$BODY$
DECLARE
	rec record;
BEGIN
	execute 'create temp table tmpt_1 on commit drop as 
		select * from test_item_info';
		
	FOR rec IN  execute 'select * from tmpt_1' LOOP
		RETURN NEXT rec;
	END LOOP;

RETURN;

END;$BODY$
  LANGUAGE 'plpgsql';

[ycjack]

Hi, rski

Thank you so much! It does work.


Jack