返回游标? 示例: CREATE FUNCTION dt2(refcursor) RETURNS refcursor AS $$ declare r record; ref refcursor; ff varchar:='SELECT "A",'; begin for r in select distinct "B" from tth1 loop ff:=ff || 'max(case when "B"='''||r."B" || '''' || ' then "C" ' || ' end ) as ' ||r."B" || ' ,'; RAISE NOTICE '123(%)', ff; end loop; ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by "A"'; open $1 for execute ff; return $1; end; $$ LANGUAGE plpgsql; BEGIN; SELECT dt2('a'); FETCH ALL IN a; commit;
CREATE OR REPLACE FUNCTION "public"."pg_runnow" () RETURNS SETOF "pg_catalog"."pg_stat_activity" AS $body$ /* New function body */ BEGIN RETURN query SELECT * FROM pg_catalog.pg_stat_activity; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; plpgsql有详细的语法,不再描述。pgsql返回结果集有个弱点,必须在定义函数时或调用函数时声明结果集的类型,比如以上例子,在定义函数时声明了结果集的类型(为pg_catalog.pg_stat_activity表类型)。 所以调用的时候就可以直接运行select * from pg_runnow()如果定义的时候没申明结果集类型,比如将以上例子SETOF "pg_catalog"."pg_stat_activity"改为setof RECORD(虚表),那么调用的时候就必须指明类型,就必须这种形式: select * from pg_runnow() as (A INTEGER,B CHAR ....),用select * from pg_runnow()就会出错假如结果集返回类型在定义和调用时都是未知的(或动态的),那就改用服务端游标(refcursor)吧。
示例:
CREATE FUNCTION dt2(refcursor) RETURNS refcursor AS $$
declare r record;
ref refcursor;
ff varchar:='SELECT "A",';
begin
for r in select distinct "B" from tth1 loop
ff:=ff || 'max(case when "B"='''||r."B" || '''' || ' then "C" ' || ' end ) as ' ||r."B" || ' ,';
RAISE NOTICE '123(%)', ff;
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by "A"';
open $1 for execute ff;
return $1;
end;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT dt2('a');
FETCH ALL IN a;
commit;
我觉得和视图功能类似,但比视图更强大。
具体看plpgsql文档
$body$
/* New function body */
BEGIN
RETURN query SELECT * FROM pg_catalog.pg_stat_activity;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
plpgsql有详细的语法,不再描述。pgsql返回结果集有个弱点,必须在定义函数时或调用函数时声明结果集的类型,比如以上例子,在定义函数时声明了结果集的类型(为pg_catalog.pg_stat_activity表类型)。
所以调用的时候就可以直接运行select * from pg_runnow()如果定义的时候没申明结果集类型,比如将以上例子SETOF "pg_catalog"."pg_stat_activity"改为setof RECORD(虚表),那么调用的时候就必须指明类型,就必须这种形式:
select * from pg_runnow() as (A INTEGER,B CHAR ....),用select * from pg_runnow()就会出错假如结果集返回类型在定义和调用时都是未知的(或动态的),那就改用服务端游标(refcursor)吧。
暂时先用视图替代了 谢谢大家!