postgreSQL中function返回结果集

来源：互联网发布：mac键盘怎么拆编辑：程序博客网时间：2024/05/17 07:37

发现pg的资料很少，去IOE的风估计要吹一段时间。pg会越来越流行。

1.返回record

CREATE OR REPLACE FUNCTION "public"."select_rs_record"(stat_end_time varchar)  RETURNS SETOF "pg_catalog"."record" AS $BODY$DECLARE select_result record;BEGIN--时间处理--遍历返回数据for select_result in SELECTT .stat_time,T .field2FROMtest TWHEREstat_time >= stat_end_time AND stat_time < stat_end_time AND time_type = '1'  loop   return next select_result;  end loop; RETURN  ; END $BODY$  LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000;

查看数据：

select stat_time,field2 from select_rs_record('2015-07-07')   AS MEMBER (stat_time VARCHAR (64),field2 VARCHAR (64));

2.直接返回Table

CREATE OR REPLACE FUNCTION "public"."select_rs_table"(stat_end_time varchar)  RETURNS TABLE(stat_time VARCHAR (64),field2 VARCHAR (64))  AS $BODY$DECLARE BEGIN--时间处理--遍历返回数据RETURN  QUERYSELECTT .stat_time,T .field2FROMtest TWHEREstat_time >= stat_end_time AND stat_time < stat_end_time AND time_type = '1';END $BODY$  LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000;

查看数据：

select stat_time,field2 from select_rs_table('2015-07-07');

0 0