数据类型(可以有模式修饰),可以是基本类型,复合类型、域类型、游标、或者可以引用一个现有表类型、字段类型(建立时转换为对应的类型)、还可以是多态类型 anyelement、anyarray,也可以是各种数据类型的数组形式。
基本类型
teledb=# CREATE OR REPLACE FUNCTION f3 (a_int integer,a_str text) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f3(1,'teledb');
NOTICE: a_int = 1 ; a_str = teledb
f3
----
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f3 (a_int integer[],a_str text[]) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f3(ARRAY[1,2,3],ARRAY['teledb','pgxz']);
NOTICE: a_int = {1,2,3} ; a_str = {teledb,pgxz}
f3
----
(1 row)
复合类型
teledb=# CREATE TYPE t_per AS
teledb-# (
teledb(# id integer,
teledb(# mc text
teledb(# );
CREATE TYPE
teledb=# CREATE OR REPLACE FUNCTION f3 (a_row public.t_per) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f3(ROW(1,'teledb')::public.t_per);
NOTICE: id = 1 ; mc = teledb
f3
----
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f3 (a_rec public.t_per[]) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'a_rec = %',a_rec;
teledb$# RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f3(ARRAY[ROW(1,'teledb'),ROW(1,'pgxz')]::public.t_per[]);
NOTICE: a_rec = {"(1,teledb)","(1,pgxz)"}
NOTICE: a_rec[1].id = 1
f3
----
(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 f3 (a_row public.t) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f3(ROW(1,'teledb')::public.t);
NOTICE: id = 1 ; mc = teledb
f3
----
(1 row)
teledb=> CREATE OR REPLACE FUNCTION f3 (a_rec public.t[]) RETURNS int AS
teledb-> $$
teledb$> BEGIN
teledb$> return a_rec[1].id;
teledb$> END;
teledb$> $$
teledb-> LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=> SELECT f3(array[row(1,'teledb'),row(2,'pgxz')]::public.t[]);
f3
----
1
(1 row)
teledb=> SELECT f3(array[t.*,t.*]::public.t[]) FROM t LIMIT 2;
f3
----
1
2
域类型
teledb=# CREATE DOMAIN xb AS TEXT CHECK
teledb-# (
teledb(# VALUE = '男'
teledb(# OR VALUE ='女'
teledb(# OR VALUE = ''
teledb(# );
CREATE DOMAIN
teledb=# CREATE OR REPLACE FUNCTION f4 (a_xb public.xb) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'a_xb = %',a_xb;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f4('男');
NOTICE: a_xb = 男
f4
----
(1 row)
teledb=# SELECT * FROM f4('她');
ERROR: value for domain xb violates check constraint "xb_check"
域类型输入参数值时会检查是否违反规则。
游标类型
teledb=# CREATE OR REPLACE FUNCTION f5 (a_ref refcursor) RETURNS void AS
teledb-# $$
teledb$# DECLARE
teledb$# v_rec record;
teledb$# BEGIN
teledb$# OPEN a_ref FOR SELECT * FROM t LIMIT 1;
teledb$# FETCH a_ref INTO v_rec;
teledb$# RAISE NOTICE 'v_rec = % ',v_rec;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f5('a');
NOTICE: v_rec = (1,teledb)
f5
----
(1 row)
teledb=# CREATE OR REPLACE FUNCTION f6 (a_ref refcursor) RETURNS refcursor AS
teledb-# $$
teledb$# BEGIN
teledb$# OPEN a_ref FOR SELECT * FROM t LIMIT 1;
teledb$# RETURN a_ref;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
-- 这里需要开启一个事务
teledb=# BEGIN;
BEGIN
teledb=# SELECT * FROM F6('a');
f6
----
a
多态类型
teledb=# CREATE OR REPLACE FUNCTION f_any(a_arg anyelement) RETURNS VOID AS
$$
BEGIN
RAISE NOTICE '%',a_arg;
END;
$$
LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f_any(1::integer);
NOTICE: 1
f_any
-------
(1 row)
teledb=# SELECT f_any('teledb'::TEXT);
NOTICE: teledb
f_any
-------
(1 row)
teledb=# SELECT f_any(ROW(1,'teledb')::public.t_per);
NOTICE: (1,teledb)
f_any
-------
(1 row)
teledb=# SELECT f_any(ARRAY[1,2]::INTEGER[]);
NOTICE: {1,2}
f_any
-------
(1 row)
teledb=# SELECT f_any(ARRAY[[1,2],[3,4],[5,6]]::INTEGER[][][]);
NOTICE: {{1,2},{3,4},{5,6}}
f_any
-------
(1 row)
注意多态类型参数函数调用时最好直接声明参数类型,否则有可能出错。
teledb=# CREATE OR REPLACE FUNCTION f_any(a_arg anyarray) RETURNS VOID AS
$$
BEGIN
RAISE NOTICE '%',a_arg;
END;
$$
LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT f_any(ARRAY['teledb','pgxz']::TEXT[]);
ERROR: function f_any(text[]) is not unique
LINE 1: SELECT f_any(ARRAY['teledb','pgxz']::TEXT[]);
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Anyelement参数如果写成数组,其意义就跟anyarray参数一致,所以f_any(a_arg anyelement)与f_any(a_arg anyarray)在调用f_any(ARRAY[1,2])时就会出现函数不是唯一化的错误(ERROR: function f_any(…) is not unique)提示。
参数默认值
PL/pgsql扩展语言函数支持给参数设置默认值。
teledb=# CREATE OR REPLACE FUNCTION f7 (a_int INTEGER DEFAULT 1) RETURNS VOID AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE 'a_int = %',a_int;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE PLPGSQL;
CREATE FUNCTION
teledb=# SELECT * FROM f7();
NOTICE: a_int = 1
f7
----
(1 row)
备注:如果原来存在一个f7() 这样的函数,则上面的执行就会出错,因为系统无法清楚到要执行那个函数,如下所示。
teledb=# CREATE OR REPLACE FUNCTION f7() RETURNS void AS
teledb-# $$
teledb$# BEGIN
teledb$# RAISE NOTICE '无参数';
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql ;
CREATE FUNCTION
teledb=# SELECT * FROM f7();
ERROR: function f7() is not unique
LINE 1: SELECT * FROM f7();
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
出错提示,f7()函数不是唯一的,这是使用上一个需要特别注意的地方。