-- 这是使用pl/pgsql语言的风格,
CREATE OR REPLACE FUNCTION function2 () RETURNS setof table1 AS
$body$
DECLARE
result record;
BEGIN
for result in select * from table1 limit 10 loop
return next result;
end loop;
return;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from function2() ;
---------------------------------------------------
-- 你甚至可以使用sql语言让代码看起来更简单:
CREATE OR REPLACE FUNCTION function1 () RETURNS SETOF table1 AS
$body$
SELECT * from table1 limit 10 ;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from function1() ;
------------------ 前提必须知道所有字段 并在select的时候声明所有字段以及类型
-- 优点 动态传递表名
CREATE OR REPLACE FUNCTION function3 (text) RETURNS SETOF record AS
$body$
declare
rec record;
begin
for rec in execute 'SELECT * from '| $1 |' limit 10' loop
return next rec ;
end loop;
return;
end
$body$
LANGUAGE 'plpgsql' ;
select * from function3('table1') as dept(view_id varchar,branch varchar,group_lower numeric);
-- refcursor 使用见 https://blog.csdn.net/yueliangdao0608/article/details/18618381