PostgreSQL函数：返回表查询结果集

一、创建测试表 create table jason01.jason123(aa varchar); insert into jason01.jason123 values('aa'); insert into jason01.jason123 values('ab'); insert into jason01.jason123 values('bb'); postgres=# select * from jason01.jason123;  aa ----  aa  ab  bb (3 rows) 二、创建函数 1、方法一：使用%rowtype CREATE OR REPLACE FUNCTION jason01.fun_resultset() RETURNS SETOF jason01.jason123 AS $$ DECLARE     r jason01. jason123%rowtype ; BEGIN     FOR r IN SELECT * FROM jason01.jason123 where aa like 'a%'     LOOP         RETURN NEXT r;     END LOOP;     RETURN; END $$ LANGUAGE plpgsql; postgres=# select jason01.fun_resultset();  fun_resultset ---------------  (aa)  (ab) (2 rows) 2、方法二：使用record类型 CREATE OR REPLACE FUNCTION jason01.fun_resultset1() RETURNS SETOF jason01.jason123 AS $$ DECLARE     r record; BEGIN     FOR r IN SELECT * FROM jason01.jason123 where aa like 'b%'     LOOP         RETURN NEXT r;     END LOOP;     RETURN; END $$ LANGUAGE plpgsql; postgres=# select jason01.fun_resultset1();  fun_resultset1 ----------------  (bb) (1 row) 3、方法三：使用cursor + record CREATE OR REPLACE FUNCTION jason01.fun_resultset2() RETURNS SETOF jason01.jason123 AS $$ DECLARE     C CURSOR FOR SELECT * FROM jason01.jason123 where aa like 'a%';     R RECORD; BEGIN     FOR r IN C     LOOP         RETURN NEXT r;     END LOOP;     RETURN; END $$ LANGUAGE plpgsql; postgres=# select jason01.fun_resultset2();  fun_resultset2 ----------------  (aa)  (ab) (2 rows)