一、IN 模式
IN 模式指的是执行函数时需要输入参数值，如下所示。
postgres=# CREATE OR REPLACE FUNCTION f1(IN a_xm text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# RETURN a_xm;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f1('tdsql_pg'); f1—— tdsql_pg(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f1(a_xm text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# RETURN a_xm;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f1('tdsql_pg'); f1—— tdsql_pg(1 行记录)
上面两种方式定义的参数效果是一样的。
二、OUT 模式
OUT 模式参数是指定了函数执行时返回的字段名及类型。
postgres=# CREATE OR REPLACE FUNCTION f1(OUT a_xm TEXT) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# a_xm:='tdsql_pg';postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=# SELECT * FROM f1(); a_xm—— tdsql_pg(1 行记录)
采用 OUT 模式参数不能用 RETURN 返回，而是要对返回的 OUT 参数直接赋值。返回值类型与参数的数据类型必需一致。参数名就是返回的字段名。
三、INOUT 模式
INOUT 模式是指参数即传入，同时又指定了返回值的字段名和类型。
postgres=# CREATE OR REPLACE FUNCTION f1(INOUT a_xm text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f1('tdsql_pg'); a_xm—— tdsql_pg(1 行记录)
值得注意的是，上面的函数跟下面的函数是相同的，即重新定义会覆盖掉。
postgres=# CREATE OR REPLACE FUNCTION f1(IN a_xm text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# RETURN 'tdsql_pg';postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=# SELECT * FROM f1('tdsql_pg'); f1—— tdsql_pg(1 行记录)
四、VARIADIC 模式
VARIADIC 模式是参数个数可变模式，系统用一个数组对传入的参数进行处理，VARIADIC 参数必需是所有最后一个声明，如下所示。
postgres=# CREATE OR REPLACE FUNCTION f1(VARIADIC a_int integer[]) RETURNS void ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_int = %',a_int;postgres$# RAISE NOTICE 'a_int[1] = %',a_int[1];postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f1(1);NOTICE: a_int = {1}NOTICE: a_int[1] = 1 f1—-(1 行记录)postgres=# SELECT f1(1,2);NOTICE: a_int = {1,2}NOTICE: a_int[1] = 1 f1—-(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f1(a_xm TEXT,VARIADIC a_int integer[]) RETURNS void ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_int = %',a_int;postgres$# RAISE NOTICE 'a_int[1] = %',a_int[1];postgres$# RAISE NOTICE 'a_xm = %',a_xm;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f1('tdsql_pg',1,2);NOTICE: a_int = {1,2}NOTICE: a_int[1] = 1NOTICE: a_xm = tdsql_pga f1—-(1 行记录)
五、参数引用
PL/pgsql 函数的参数是以$1,$2这样标识符来进行传递，也支持命名参数，所以参数的定义可以用下面的方式。
六、无命名参数
postgres=# CREATE OR REPLACE FUNCTION f2(text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# RETURN $1;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f2('tdsql_pg'); f2—— tdsql_pg(1 行记录)
七、给标识符指定别名
postgres=# CREATE OR REPLACE FUNCTION f2(text) RETURNS TEXT ASpostgres-# $$postgres$# DECLAREpostgres$# a_xm ALIAS FOR $1; –a_xm是$1的别名postgres$# BEGINpostgres$# RETURN a_xm;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f2('tdsql_pg'); f2—— tdsql_pg(1 行记录)
八、命名参数
postgres=# CREATE OR REPLACE FUNCTION f2(a_xm text) RETURNS TEXT ASpostgres-# $$postgres$# DECLAREpostgres$# v_xm ALIAS FOR $1;postgres$# BEGINpostgres$# RAISE NOTICE 'a_xm = % ; v_xm = % ; $1 = %',a_xm,v_xm,$1;postgres$# RETURN $1;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f2('tdsql_pg');NOTICE: a_xm = tdsql_pg ; v_xm = tdsql_pg ; $1 = tdsql_pg f2—— tdsql_pg(1 行记录)
九、参数数据类型
数据类型(可以有模式修饰)，可以是基本类型，复合类型、域类型、游标、或者可以引用一个现有表类型、字段类型(建立时转换为对应的类型)、还可以是多态类型 anyelement、anyarray，也可以是各种数据类型的数组形式。
十、基本类型
postgres=# CREATE OR REPLACE FUNCTION f3 (a_int integer,a_str text) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=# SELECT * FROM f3(1,'tdsql_pg');NOTICE: a_int = 1 ; a_str = tdsql_pg f3—-(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f3 (a_int integer[],a_str text[]) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=# SELECT f3(ARRAY[1,2,3],ARRAY['tdsql_pg','pgxz']);NOTICE: a_int = {1,2,3} ; a_str = {tdsql_pg,pgxz} f3—-(1 行记录)
十一、复合类型
postgres=# CREATE TYPE t_per ASpostgres-# (postgres(# id integer,postgres(# mc textpostgres(# );ERROR: type "t_per" already existspostgres=# CREATE OR REPLACE FUNCTION f3 (a_row public.t_per) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f3(ROW(1,'tdsql_pg')::public.t_per);NOTICE: id = 1 ; mc = tdsql_pg f3—-(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f3 (a_rec public.t_per[]) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_rec = %',a_rec;postgres$# RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f3(ARRAY[ROW(1,'tdsql_pg'),ROW(1,'pgxz')]::public.t_per[]);NOTICE: a_rec = {"(1,tdsql_pg)","(1,pgxz)"}NOTICE: a_rec[1].id = 1 f3—-(1 行记录)
十二、行类型
postgres=# \\d t 资料表 "public.t" 栏位 | 型别 | 修饰词——+———+——– id | integer | mc | text |postgres=# CREATE OR REPLACE FUNCTION f3 (a_row public.t) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f3(ROW(1,'tdsql_pg'));NOTICE: id = 1 ; mc = tdsql_pg f3—-(1 行记录)postgres=# SELECT f3(t.*) FROM t LIMIT 1;NOTICE: id = 1 ; mc = tdsql_pg f3—-(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f3 (a_rec public.t[]) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_rec = %',a_rec;postgres$# RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f3(array[row(1,'tdsql_pg'),row(1,'pgxz')]::public.t[]);NOTICE: a_rec = {"(1,tdsql_pg)","(1,pgxz)"}NOTICE: a_rec[1].id = 1 f3—-(1 行记录)postgres=# SELECT f3(array[t.*,t.*]::public.t[]) FROM t LIMIT 2;NOTICE: a_rec = {"(1,tdsql_pg)","(1,tdsql_pg)"}NOTICE: a_rec[1].id = 1NOTICE: a_rec = {"(2,pgxz)","(2,pgxz)"}NOTICE: a_rec[1].id = 2 f3—-(2 行记录)
十三、域类型
postgres=# CREATE DOMAIN xb AS TEXT CHECKpostgres-# (postgres(# VALUE = '男'postgres(# OR VALUE ='女'postgres(# OR VALUE = ''postgres(# );CREATE DOMAINpostgres=#postgres=# CREATE OR REPLACE FUNCTION f4 (a_xb public.xb) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_xb = %',a_xb;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f4('男');NOTICE: a_xb = 男 f4—-(1 行记录)postgres=# SELECT * FROM f4('她');ERROR: value for domain xb violates check constraint "xb_check"postgres=#
域类型输入参数值时会检查是否违反规则。
十四、游标类型
postgres=# CREATE OR REPLACE FUNCTION f5 (a_ref refcursor) RETURNS void ASpostgres-# $$postgres$# DECLAREpostgres$# v_rec record;postgres$# BEGINpostgres$# OPEN a_ref FOR SELECT * FROM t LIMIT 1;postgres$# FETCH a_ref INTO v_rec;postgres$# RAISE NOTICE 'v_rec = % ',v_rec;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f5('a');NOTICE: v_rec = (1,tdsql_pg) f5—-(1 行记录)postgres=# CREATE OR REPLACE FUNCTION f6 (a_ref refcursor) RETURNS refcursor ASpostgres-# $$postgres$# BEGINpostgres$# OPEN a_ref FOR SELECT * FROM t LIMIT 1;postgres$# RETURN a_ref;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=##注意这里需要开启一个事务postgres=# BEGIN;BEGINpostgres=# SELECT * FROM f6('a'); f6—- a(1 行记录)postgres=# FETCH ALL FROM a; id | mc—-+—— 1 | tdsql_pg(1 行记录)
十五、多态类型
postgres=# CREATE OR REPLACE FUNCTION f_any(a_arg anyelement) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE '%',a_arg;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f_any(1::integer);NOTICE: 1 f_any——-(1 行记录)postgres=# SELECT f_any('tdsql_pg'::TEXT);NOTICE: tdsql_pg f_any——-(1 行记录)postgres=# SELECT f_any(ROW(1,'tdsql_pg')::public.t_rec);NOTICE: (1,tdsql_pg) f_any——-(1 行记录)postgres=# SELECT f_any(ARRAY[1,2]::INTEGER[]);NOTICE: {1,2} f_any——-(1 行记录)postgres=# SELECT f_any(ARRAY[[1,2],[3,4],[5,6]]::INTEGER[][][]);NOTICE: {{1,2},{3,4},{5,6}} f_any——-(1 行记录)
注意多态类型参数函数调用时最好直接声明参数类型，否则有可能出错。
postgres=# CREATE OR REPLACE FUNCTION fanyarray(aarg anyarray) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE '%',a_arg;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT f_any_array(ARRAY['tdsql_pg','pgxz']::TEXT[]);NOTICE: {tdsql_pg,pgxz} f_any_array————-(1 行记录)postgres=# SELECT f_any_array(ARRAY[ARRAY['tdsql_pg','pgxz'],ARRAY['tdsql_pg','Tencent']]::TEXT[][]);NOTICE: {{tdsql_pg,pgxz},{tdsql_pg,Tencent}} f_any_array————-(1 行记录)
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 扩展语言函数支持给参数设置默认值。
postgres=# CREATE OR REPLACE FUNCTION f7 (a_int INTEGER DEFAULT 1) RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE 'a_int = %',a_int;postgres$# END;postgres$# $$postgres-# LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f7();NOTICE: a_int = 1 f7—-(1 行记录)
备注：如果原来存在一个 f7() 这样的函数，则上面的执行就会出错，因为系统无法清楚到要执行那个函数，如下所示。
postgres=# CREATE OR REPLACE FUNCTION f7() RETURNS void ASpostgres-# $$postgres$# BEGINpostgres$# RAISE NOTICE '无参数';postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql ;CREATE FUNCTIONpostgres=#postgres=# SELECT * FROM f7();ERROR: function f7() is not unique第1行SELECT * FROM f7(); ^提示: Could not choose a best candidate function. You might need to add explicit type casts.postgres=#
出错提示，f7() 函数不是唯一的，这是使用上一个需要特别注意的地方。