返回值類型介紹
更新時間 2025-02-14 10:22:12
最近更新時間: 2025-02-14 10:22:12
分享文章
本頁介紹天翼云TeleDB數據庫PL/pgsql函數的返回值類型。
沒有返回值
teledb=# CREATE OR REPLACE FUNCTION f8() RETURNS void AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE '不用返回值，函數體可以有或沒有return語句';
teledb$# RETURN ;--這一句可以有，也可以沒有
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# select * from f8();
NOTICE: 不用返回值，函數體可以有或沒有return語句
f8
----
(1 row)返回簡單類型
teledb=# CREATE OR REPLACE FUNCTION f9() RETURNS TEXT AS
$$
BEGIN
RETURN 'teledb';
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f9() t(a_xm);
a_xm
---------
teledb
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f9(OUT a_xm TEXT) RETURNS TEXT AS
$$
BEGIN
a_xm:='teledb';
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f9();
a_xm
---------
teledb
(1 row)上面兩個函數其實就是同一個函數，建立時如果不加OR REPLACE 則會提示已經存在。
teledb=# CREATE OR REPLACE FUNCTION f10() RETURNS TEXT[] AS
$$
BEGIN
RETURN ARRAY['teledb','pgxz'];
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f10();
f10
----------------
{teledb,pgxz}
(1 row)返回一個復合類型
teledb=# CREATE TYPE t_rec AS
teledb-# (
teledb(# id integer,
teledb(# mc text
teledb(# );
CREATE TYPE
teledb=# CREATE OR REPLACE FUNCTION f11() RETURNS t_rec AS
teledb-# $$
teledb$# DECLARE
teledb$# v_rec public.t_rec;
teledb$# BEGIN
teledb$# v_rec.id:=1;
teledb$# v_rec.mc='teledb';
teledb$# RETURN v_rec;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f11();
id | mc
----+---------
1 | teledb
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f12() RETURNS t_rec[] AS
teledb-# $$
teledb$# BEGIN
teledb$# RETURN ARRAY[ROW(1,'teledb'),ROW(1,'pgxz')]::t_rec[];
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql
teledb-# ;
CREATE FUNCTION
teledb=# SELECT * FROM f12();
f12
----------------------------
{"(1,teledb)","(1,pgxz)"}
(1 row)返回行類型
teledb=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | not null |
mc | character varying | | |
teledb=# CREATE OR REPLACE FUNCTION f13() RETURNS public.t AS
teledb-# $$
teledb$# DECLARE
teledb$# v_rec public.t%ROWTYPE;
teledb$# BEGIN
teledb$# SELECT * INTO v_rec FROM public.t LIMIT 1;
teledb$# RETURN v_rec;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f13();
id | mc
----+---------
1 | teledb
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f14() RETURNS public.t[] AS
teledb-# $$
teledb$# DECLARE
teledb$# v_rec public.t[];
teledb$# BEGIN
teledb$# SELECT ARRAY[ROW(t.*),ROW(t.*)]::public.t[] INTO v_rec FROM public.t LIMIT 1;
teledb$# RETURN v_rec;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f14();
f14
-------------------------------
{"(1,teledb)","(1,teledb)"}
(1 row)返回TABLE類型
teledb=# DROP FUNCTION f14();
DROP FUNCTION
teledb=# CREATE FUNCTION f14() RETURNS TABLE(a_id integer, a_nc text) AS
teledb-# $$
teledb$# BEGIN
teledb$# RETURN QUERY SELECT 1::integer,'teledb'::Text;
teledb$# END;
teledb$# $$LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT * FROM f14();
a_id | a_nc
------+---------
1 | teledb
(1 row)返回RECORD類型
teledb=# CREATE OR REPLACE FUNCTION f15() RETURNS RECORD AS
teledb-# $$
teledb$# DECLARE
teledb$# v_rec RECORD;
teledb$# BEGIN
teledb$# v_rec:=ROW(1::integer,'teledb'::text,'pgxz'::text);
teledb$# RETURN v_rec;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f15();
f15
------------------
(1,teledb,pgxz)
(1 row)
teledb=# SELECT * FROM f15() t(id integer,xm text,xl text);
id | xm | xl
----+---------+------
1 | teledb | pgxz
(1 row)返回一個游標
teledb=# CREATE OR REPLACE FUNCTION f16() RETURNS refcursor AS
teledb-# $$
teledb$# DECLARE
teledb$# v_ref refcursor;
teledb$# BEGIN
teledb$# OPEN v_ref FOR SELECT * FROM public.t;
teledb$# RETURN v_ref;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# BEGIN;
BEGIN
teledb=# SELECT * FROM f16();
f16
--------------------
<unnamed portal 4>
(1 row)
teledb=# FETCH ALL FROM "<unnamed portal 4>";
id | mc
----+---------
1 | teledb
2 | abcd
(2 rows)
teledb=# END;
COMMIT
teledb=# CREATE OR REPLACE FUNCTION f16(a_ref refcursor) RETURNS refcursor AS
teledb-# $$
teledb$# BEGIN
teledb$# OPEN a_ref FOR SELECT * FROM public.t;
teledb$# RETURN a_ref;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# BEGIN;
BEGIN
teledb=# SELECT * FROM f16('a');
f16
-----
a
(1 row)
teledb=# FETCH ALL FROM a;
id | mc
----+---------
1 | teledb
2 | abcd
(2 rows)
teledb=# END;
COMMIT返回記錄集
teledb=# CREATE OR REPLACE FUNCTION f17() RETURNS SETOF TEXT AS
teledb-# $$
teledb$# BEGIN
teledb$# RETURN NEXT 'teledb'::text;
teledb$# RETURN NEXT 'pgxz'::text;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f17();
f17
---------
teledb
pgxz
(2 rows)
teledb=# CREATE OR REPLACE FUNCTION f18() RETURNS SETOF public.t AS
teledb-# $$
teledb$# DECLARE
teledb$# --使用行類型返回
teledb$# v_rec public.t%ROWTYPE;
teledb$# BEGIN
teledb$# FOR v_rec IN SELECT * FROM t ORDER BY id LOOP
teledb$# RETURN NEXT v_rec;
teledb$# END LOOP;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f18();
id | mc
----+---------
1 | teledb
2 | abcd
(2 rows)
teledb=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | not null |
yhm | text | | |
nc | text | | |
mm | character varying | | |
teledb=# CREATE OR REPLACE FUNCTION f19() RETURNS SETOF public.t_rec AS
teledb-# $$
teledb$# DECLARE
teledb$# --使用已經定義的結構類型返回
teledb$# v_rec public.t_rec;
teledb$# BEGIN
teledb$# FOR v_rec IN SELECT id,yhm FROM t1 ORDER BY id LOOP
teledb$# RETURN NEXT v_rec;
teledb$# END LOOP;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f19();
id | mc
----+-----------
1 | teledb
2 | teledb-1
(2 rows)
teledb=# CREATE OR REPLACE FUNCTION f20(a_int integer) RETURNS SETOF record AS
teledb-# $$
teledb$# DECLARE
teledb$# --a_int定義返回的字段數，實現動態列返回
teledb$# v_rec record;
teledb$# v_sql text;
teledb$# BEGIN
teledb$# IF a_int = 2 THEN
teledb$# v_sql:='SELECT id,yhm FROM t1 ORDER BY id ';
teledb$# ELSE
teledb$# v_sql:='SELECT id,yhm,nc FROM t1 ORDER BY id';
teledb$# END IF;
teledb$# FOR v_rec IN EXECUTE v_sql LOOP
teledb$# RETURN NEXT v_rec;
teledb$# END LOOP;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f20(2) t(id integer,yhm text);
id | yhm
----+-----------
1 | teledb
2 | teledb-1
(2 rows)
teledb=# SELECT * FROM f20(3) t(id integer,yhm text,nc text);
id | yhm | nc
----+-----------+-----------
1 | teledb | teledb
2 | teledb-1 | teledb-1
(2 rows)
teledb=# CREATE OR REPLACE FUNCTION f21(OUT a_id integer,OUT a_yhm TEXT) RETURNS SETOF record AS
teledb-# $$
teledb$# DECLARE
teledb$# --使用out返回
teledb$# v_rec record;
teledb$# BEGIN
teledb$# FOR v_rec IN SELECT id,yhm FROM t1 LOOP
teledb$# a_id:=v_rec.id;
teledb$# a_yhm:=v_rec.yhm;
teledb$# RETURN NEXT;
teledb$# END LOOP;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f21();
a_id | a_yhm
------+-----------
1 | teledb
2 | teledb-1
(2 rows)
teledb=# CREATE OR REPLACE FUNCTION f22() RETURNS SETOF refcursor AS
teledb-# $$
teledb$# DECLARE
teledb$# --返回游標集
teledb$# v_ref1 REFCURSOR;
teledb$# v_ref2 REFCURSOR;
teledb$# BEGIN
teledb$# OPEN v_ref1 FOR SELECT * FROM t;
teledb$# OPEN v_ref2 FOR SELECT * FROM t1;
teledb$# RETURN NEXT v_ref1;
teledb$# RETURN NEXT v_ref2;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# BEGIN;
BEGIN
teledb=# SELECT * FROM f22();
f22
---------------------
<unnamed portal 10>
<unnamed portal 11>
(2 rows)
teledb=# FETCH ALL FROM "<unnamed portal 10>";
id | mc
----+---------
1 | teledb
2 | abcd
(2 rows)
teledb=# FETCH ALL FROM "<unnamed portal 11>";
id | yhm | nc | mm
----+-----------+-----------+---------
1 | teledb | teledb | 4134
2 | teledb-1 | teledb-1 | sga4134
(2 rows)
teledb=# COMMIT;
COMMIT
teledb=# CREATE OR REPLACE FUNCTION f22(a_ref1 refcursor,a_ref2 refcursor) RETURNS SETOF refcursor AS
teledb-# $$
teledb$# BEGIN
teledb$# --指定游標名稱
teledb$# OPEN a_ref1 FOR SELECT * FROM t;
teledb$# OPEN a_ref2 FOR SELECT * FROM t1;
teledb$# RETURN NEXT a_ref1;
teledb$# RETURN NEXT a_ref2;
teledb$# RETURN ;--最后的RETURN可以加，也可以不加上去
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=#
teledb=# BEGIN;
BEGIN
teledb=# SELECT * FROM f22('a','b');
f22
-----
a
b
(2 rows)
teledb=# FETCH ALL FROM "a";
id | mc
----+---------
1 | teledb
2 | abcd
(2 rows)
teledb=# FETCH ALL FROM "b";
id | yhm | nc | mm
----+-----------+-----------+---------
1 | teledb | teledb | 4134
2 | teledb-1 | teledb-1 | sga4134
(2 rows)
teledb=# COMMIT;
COMMIT返回多態類型
teledb=# CREATE OR REPLACE FUNCTION f23(a_arg anyelement) RETURNS anyelement AS
teledb-# $$
teledb$# BEGIN
teledb$# RETURN a_arg;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f23('teledb'::text);
f23
---------
teledb
(1 row)
teledb=# SELECT * FROM f23(1::integer);
f23
-----
1
(1 row)
teledb=# SELECT * FROM f23(ARRAY['teledb','pgxz']);
f23
----------------
{teledb,pgxz}
(1 row)
teledb=# SELECT * FROM f23(ROW(1,'teledb')::public.t_rec);
id | mc
----+---------
1 | teledb
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f24(a_arg ANYARRAY) RETURNS anyarray AS
teledb-# $$
teledb$# BEGIN
teledb$# RETURN a_arg;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f24(ARRAY[1,2]::INTEGER[]);
f24
-------
{1,2}
(1 row)
teledb=# SELECT f24(ARRAY[t1.*]) FROM t1;
f24
-------------------------------------
{"(1,teledb,teledb,4134)"}
{"(2,teledb-1,teledb-1,sga4134)"}
(2 rows)返回數據類型如果是多態，則函數最少需要定義一個多態參數。
