参数详细介绍

最近更新时间:2022-09-02 15:30:29

我的收藏

参数模式

参数模式总共有三种,分别是 IN(传入参数,这个是默认方式)、OUT(返回值参数)、INOUT(传入返回值参数),下面介绍这几个参数模式的用法。

IN 模式

IN 模式指的是执行函数时需要输入参数值,如下所示。
postgres=# CREATE OR REPLACE FUNCTION f1(IN a_xm text) RETURNS TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$# RETURN a_xm;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT f1('tdsql_pg');
f1
------
tdsql_pg
(1 行记录)
postgres=# CREATE OR REPLACE FUNCTION f1(a_xm text) RETURNS TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$# RETURN a_xm;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f1('tdsql_pg');
f1
------
tdsql_pg
(1 行记录)
上面两种方式定义的参数效果是一样的。

OUT 模式

OUT 模式参数是指定了函数执行时返回的字段名及类型。
postgres=# CREATE OR REPLACE FUNCTION f1(OUT a_xm TEXT) RETURNS TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$# a_xm:='tdsql_pg';
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=# 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 AS
postgres-# $$
postgres$# BEGIN
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f1('tdsql_pg');
a_xm
------
tdsql_pg
(1 行记录)
值得注意的是,上面的函数跟下面的函数是相同的,即重新定义会覆盖掉。
postgres=# CREATE OR REPLACE FUNCTION f1(IN a_xm text) RETURNS TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$# RETURN 'tdsql_pg';
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=# SELECT * FROM f1('tdsql_pg');
f1
------
tdsql_pg
(1 行记录)

VARIADIC 模式

VARIADIC 模式是参数个数可变模式,系统用一个数组对传入的参数进行处理,VARIADIC 参数必需是所有最后一个声明,如下所示。
postgres=# CREATE OR REPLACE FUNCTION f1(VARIADIC a_int integer[]) RETURNS void AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_int = %',a_int;
postgres$# RAISE NOTICE 'a_int[1] = %',a_int[1];
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# 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 FUNCTION
postgres=#
postgres=# SELECT f1('tdsql_pg',1,2);
NOTICE: a_int = {1,2}
NOTICE: a_int[1] = 1
NOTICE: a_xm = tdsql_pga
f1
----

(1 行记录)

参数引用

PL/pgsql 函数的参数是以$1,$2这样标识符来进行传递,也支持命名参数,所以参数的定义可以用下面的方式。

无命名参数

postgres=# CREATE OR REPLACE FUNCTION f2(text) RETURNS TEXT AS
postgres-# $$
postgres$# BEGIN
postgres$# RETURN $1;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f2('tdsql_pg');
f2
------
tdsql_pg
(1 行记录)

给标识符指定别名

postgres=# CREATE OR REPLACE FUNCTION f2(text) RETURNS TEXT AS
postgres-# $$
postgres$# DECLARE
postgres$# a_xm ALIAS FOR $1; --a_xm是$1的别名
postgres$# BEGIN
postgres$# RETURN a_xm;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f2('tdsql_pg');
f2
------
tdsql_pg
(1 行记录)

命名参数

postgres=# CREATE OR REPLACE FUNCTION f2(a_xm text) RETURNS TEXT AS
postgres-# $$
postgres$# DECLARE
postgres$# v_xm ALIAS FOR $1;
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_xm = % ; v_xm = % ; $1 = %',a_xm,v_xm,$1;
postgres$# RETURN $1;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=# 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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=# 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 AS
postgres-# (
postgres(# id integer,
postgres(# mc text
postgres(# );
ERROR: type "t_per" already exists
postgres=# CREATE OR REPLACE FUNCTION f3 (a_row public.t_per) RETURNS VOID AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_rec = %',a_rec;
postgres$# RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_rec = %',a_rec;
postgres$# RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 = 1
NOTICE: a_rec = {"(2,pgxz)","(2,pgxz)"}
NOTICE: a_rec[1].id = 2
f3
----

(2 行记录)

域类型

postgres=# CREATE DOMAIN xb AS TEXT CHECK
postgres-# (
postgres(# VALUE = '男'
postgres(# OR VALUE ='女'
postgres(# OR VALUE = ''
postgres(# );
CREATE DOMAIN
postgres=#
postgres=# CREATE OR REPLACE FUNCTION f4 (a_xb public.xb) RETURNS VOID AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_xb = %',a_xb;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# DECLARE
postgres$# v_rec record;
postgres$# BEGIN
postgres$# 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 FUNCTION
postgres=#
postgres=# SELECT * FROM f5('a');
NOTICE: v_rec = (1,tdsql_pg)
f5
----

(1 行记录)
postgres=# CREATE OR REPLACE FUNCTION f6 (a_ref refcursor) RETURNS refcursor AS
postgres-# $$
postgres$# BEGIN
postgres$# OPEN a_ref FOR SELECT * FROM t LIMIT 1;
postgres$# RETURN a_ref;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
#注意这里需要开启一个事务
postgres=# BEGIN;
BEGIN
postgres=# 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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE '%',a_arg;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE '%',a_arg;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
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 AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE 'a_int = %',a_int;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM f7();
NOTICE: a_int = 1
f7
----

(1 行记录)
备注:如果原来存在一个 f7() 这样的函数,则上面的执行就会出错,因为系统无法清楚到要执行那个函数,如下所示。
postgres=# CREATE OR REPLACE FUNCTION f7() RETURNS void AS
postgres-# $$
postgres$# BEGIN
postgres$# RAISE NOTICE '无参数';
postgres$# END;
postgres$# $$
postgres-# LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=#
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() 函数不是唯一的,这是使用上一个需要特别注意的地方。