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;