通过游标返回记录集合

通过游标返回记录集合

• 经过测试，发现三种方式都可以成功返回记录集合

• 实例1

CREATE OR REPLACE FUNCTION func_test_rowtype() 
RETURNS SETOF test1
AS
$BODY$
DECLARE
	rec test1%rowtype;
BEGIN
		  for rec in select s.* from test1 s join test2 t on (s.res_code=t.phy_res_code) loop
		  	return next rec;
		  end loop;

          return ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

• 实例2

CREATE OR REPLACE FUNCTION func_test_tabletype() 
RETURNS SETOF test1
AS
$BODY$
DECLARE
	rec test1;
BEGIN
		  for rec in select s.* from test1 s join test2 t on (s.res_code=t.phy_res_code) loop
		  	return next rec;
		  end loop;

          return ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

• 实例3

CREATE OR REPLACE FUNCTION func_test_record() 
RETURNS SETOF test1
AS
$BODY$
DECLARE
	rec test1;
BEGIN
		  for rec in select s.* from test1 s join test2 t on (s.res_code=t.phy_res_code) loop
		  	return next rec;
		  end loop;

          return ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

经过测试，发现三种方式都可以成功返回记录集合