没有返回值

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)

返回数据类型如果是多态，则函数最少需要定义一个多态参数。