TDSQL PostgreSQL版参数详细介绍-开发指南-文档中心-腾讯云

参数模式
参数模式总共有三种，分别是 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() 函数不是唯一的，这是使用上一个需要特别注意的地方。
参数详细介绍

本页目录：

参数模式

IN 模式

OUT 模式

INOUT 模式

VARIADIC 模式

参数引用

无命名参数

给标识符指定别名

命名参数

参数数据类型

基本类型

复合类型

行类型

域类型

游标类型

多态类型

参数默认值
