PG返回结果集

• 实例1

CREATE OR REPLACE FUNCTION func_test_cursor() 
RETURNS setof tbl_user 
AS
$BODY$
DECLARE
rec record;
BEGIN
   for rec in select * from tbl_user 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_query() 
RETURNS setof tbl_user 
AS
$BODY$
DECLARE
rec record;
BEGIN
	return query select * from tbl_user ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

• 实例3

CREATE OR REPLACE FUNCTION func_test() 
RETURNS setof test
AS
$BODY$
DECLARE
	rec  record;
BEGIN
	for i in 0..2 loop
		for rec in execute 'select * from test order by id offset '||i ||' limit 1 ' loop
			return next rec;
		end loop;
	end loop;
          RETURN ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

• 实例4

drop type if exists tbl_version_ext;
create type tbl_version_ext as (
	autoid bigint,
	version_type varchar,
	inner_version varchar,
	outer_version varchar,
	name varchar
);
CREATE OR REPLACE FUNCTION func_test() 
RETURNS setof tbl_version_ext 
AS
$BODY$
DECLARE
	tmp_tbl_version_ext tbl_version_ext;
	rec record;
BEGIN
	for rec in select * from tbl_version loop
		tmp_tbl_version_ext.autoid=rec.autoid;
		tmp_tbl_version_ext.version_type=rec.version_type;
		tmp_tbl_version_ext.inner_version=rec.inner_version;
		tmp_tbl_version_ext.outer_version=rec.outer_version;
		return next tmp_tbl_version_ext;
	end loop;
	return ;
          
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;