返回记录集

• 复制
  复制成功

postgres=# CREATE OR REPLACE FUNCTION f17() RETURNS SETOF TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$#     RETURN NEXT 'TDSQLPG::text;
postgres$#     RETURN NEXT 'pgxz'::text;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f17();
  f17
--------
 TDSQLPG
 pgxz
(2 行记录)
postgres=#
postgres=#
postgres=# CREATE OR REPLACE FUNCTION f18() RETURNS SETOF public.t AS
postgres-# $$
postgres$# DECLARE
postgres$#                           #使用行类型返回
postgres$#     v_rec public.t%ROWTYPE;
postgres$# BEGIN
postgres$#     FOR v_rec IN SELECT * FROM t ORDER BY id LOOP
postgres$#         RETURN NEXT v_rec;
postgres$#     END LOOP;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f18();
 id |   mc
----+--------
  1 | TDSQLPG
  2 | pgxz
(2 行记录)
postgres=# \d t1
      资料表 "public.t1"
 栏位 |     型别      | 修饰词
------+---------------+--------
 id   | integer       | 非空
 yhm  | text          | 非空
 nc   | text          | 非空
 mm   | character(32) | 非空
索引：
    "t1_pkey" PRIMARY KEY, btree (id)
    "t1_yhm_key" UNIQUE CONSTRAINT, btree (yhm)
postgres=# CREATE OR REPLACE FUNCTION f19() RETURNS SETOF public.t_rec AS
postgres-# $$
postgres$# DECLARE
postgres$#     #使用已经定义的结构类型返回
postgres$#     v_rec public.t_rec;
postgres$# BEGIN
postgres$#     FOR v_rec IN SELECT id,yhm FROM t1 ORDER BY id LOOP
postgres$#         RETURN NEXT v_rec;
postgres$#     END LOOP;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f19();
 id |   mc
----+--------
  1 | TDSQLPG
  2 | pgxc
  3 | pgxz
(3 行记录)
postgres=# CREATE OR REPLACE FUNCTION f20(a_int integer) RETURNS SETOF record AS
postgres-# $$
postgres$# DECLARE
postgres$#                              #a_int定义返回的字段数，实现动态列返回
postgres$#     v_rec record;
postgres$#     v_sql text;
postgres$# BEGIN
postgres$#     IF a_int = 2 THEN
postgres$#         v_sql:='SELECT id,yhm FROM t1 ORDER BY id ';
postgres$#     ELSE
postgres$#         v_sql:='SELECT id,yhm,nc FROM t1 ORDER BY id';
postgres$#     END IF;
postgres$#     FOR v_rec IN EXECUTE v_sql LOOP
postgres$#         RETURN NEXT v_rec;
postgres$#     END LOOP;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=# SELECT * FROM f20(2) t(id integer,yhm text);
 id |  yhm
----+--------
  1 | TDSQLPG
  2 | pgxc
  3 | pgxz
(3 行记录)
postgres=# SELECT * FROM f20(3) t(id integer,yhm text,nc text);
 id |  yhm   |   nc
----+--------+--------
  1 | TDSQLPG   | TDSQLPG
  2 | pgxc  | pgxc
  3 | pgxz | pgxz
(3 行记录)
postgres=# CREATE OR REPLACE FUNCTION f21(OUT a_id integer,OUT a_yhm TEXT) RETURNS SETOF record AS
postgres-# $$
postgres$# DECLARE
postgres$#                                             #使用out返回
postgres$#     v_rec record;
postgres$# BEGIN
postgres$#     FOR v_rec IN SELECT id,yhm FROM t1 LOOP
postgres$#         a_id:=v_rec.id;
postgres$#         a_yhm:=v_rec.yhm;
postgres$#         RETURN NEXT;
postgres$#     END LOOP;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f21();
 a_id | a_yhm
------+--------
    1 | TDSQLPG
    2 | pgxc
    3 | pgxz
(3 行记录)
postgres=# CREATE OR REPLACE FUNCTION f22() RETURNS SETOF refcursor AS
postgres-# $$
postgres$# DECLARE
postgres$#     #返回游标集
postgres$#     v_ref1 REFCURSOR;
postgres$#     v_ref2 REFCURSOR;
postgres$# BEGIN
postgres$#     OPEN v_ref1 FOR SELECT * FROM t;
postgres$#     OPEN v_ref2 FOR SELECT * FROM t1;
postgres$#     RETURN NEXT v_ref1;
postgres$#     RETURN NEXT v_ref2;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# BEGIN;
BEGIN
postgres=# SELECT * FROM f22();
         f22
---------------------
 <unnamed portal 13>
 <unnamed portal 14>
(2 行记录)
postgres=# FETCH ALL FROM "<unnamed portal 13>";
 id |   mc
----+--------
  1 | TDSQLPG
  2 | pgxz
(2 行记录)
postgres=# FETCH ALL FROM "<unnamed portal 14>";
 id |  yhm   |   nc   |                mm
----+--------+--------+----------------------------------
  1 | TDSQLPG   | TDSQLPG   | 202cb962ac59075b964b07152d234b70
  2 | pgxc  | pgxc | 202cb962ac59075b964b07152d234b70
  3 | pgxz | pgxz | 202cb962ac59075b964b07152d234b70
(3 行记录)
postgres=# COMMIT;
COMMIT
postgres=# CREATE OR REPLACE FUNCTION f22(a_ref1 refcursor,a_ref2 refcursor) RETURNS SETOF refcursor AS
postgres-# $$
postgres$# BEGIN
postgres$#     #指定游标名称
postgres$#     OPEN a_ref1 FOR SELECT * FROM t;
postgres$#     OPEN a_ref2 FOR SELECT * FROM t1;
postgres$#     RETURN NEXT a_ref1;
postgres$#     RETURN NEXT a_ref2;
postgres$#     RETURN ;                        #最后的RETURN可以加，也可以不加上去
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# BEGIN;
BEGIN
postgres=#
postgres=# SELECT * FROM f22('a','b');
 f22
-----
 a
 b
(2 行记录)
postgres=# FETCH ALL FROM "a";
 id |   mc
----+--------
  1 | TDSQLPG
  2 | pgxz
(2 行记录)
postgres=# FETCH ALL FROM "b";
 id |  yhm   |   nc   |                mm
----+--------+--------+----------------------------------
  1 | TDSQLPG   | TDSQLPG   | 202cb962ac59075b964b07152d234b70
  2 | pgxc  | pgxc | 202cb962ac59075b964b07152d234b70
  3 | pgxz | pgxz | 202cb962ac59075b964b07152d234b70
(3 行记录)
postgres=# COMMIT;
COMMIT