参数模式
存储过程中,参数模式当前只支持 IN (传入参数,这个是默认方式) 。
参数引用
无命名参数
postgres=# CREATE OR REPLACE PROCEDURE p_unname(text) AS$$BEGINraise notice '$1=%',$1;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# call ps_unname('tdsql_pg');NOTICE: $1=tdsql_pgCALLpostgres=#
给标识符指定别名
postgres=# CREATE OR REPLACE PROCEDURE p_specify_name(text) AS$$DECLAREa_xm ALIAS FOR $1; --a_xm是$1的别名BEGINraise notice '$1=%',a_xm;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_specify_name('tdsql_pg');NOTICE: $1=tdsql_pgCALLpostgres=#
命名参数
postgres=# CREATE OR REPLACE PROCEDURE p_name(a_xm text) AS$$BEGINraise notice '$1=%',a_xm;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# call p_name('tdsql_pg');NOTICE: $1=tdsql_pgCALLpostgres=#
参数数据类型
数据类型(可以有模式修饰),可以是基本类型,复合类型、域类型、游标、或者可以引用一个现有表类型、字段类型(建立时转换为对应的类型)、还可以是多态类型 anyelement、anyarray,也可以是各种数据类型的数组形式。
基本类型
postgres=# CREATE OR REPLACE PROCEDURE p_base_para (a_int integer,a_str text) AS$$BEGINRAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=#CALL p_base_para(1,'tdsql_pg');NOTICE: a_int = 1 ; a_str = tdsql_pgCALLpostgres=#postgres=# CREATE OR REPLACE PROCEDURE p_base_array (a_int integer[],a_str text[]) AS$$BEGINRAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_base_array(ARRAY[1,2,3],ARRAY['tdsql_pg','pgxz']);NOTICE: a_int = {1,2,3} ; a_str = {tdsql_pg,pgxz}CALLpostgres=#
复合类型
postgres=# CREATE TYPE public.t_per AS(id integer,mc text);CREATE TYPEpostgres=# CREATE OR REPLACE PROCEDURE p_type (a_row public.t_per) AS $$BEGINRAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_type(ROW(1,'tdsql_pg')::public.t_per);NOTICE: id = 1 ; mc = tdsql_pgCALLpostgres=#
复合数组
postgres=# CREATE OR REPLACE PROCEDURE p_type_array (a_rec public.t_per[]) AS$$BEGINRAISE NOTICE 'a_rec = %',a_rec;RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_type_array (ARRAY[ROW(1,'tdsql_pg'),ROW(1,'pgxz')]::public.t_per[]);NOTICE: a_rec = {"(1,tdsql_pg)","(1,pgxz)"}NOTICE: a_rec[1].id = 1CALLpostgres=#
行类型
postgres=# create table public.t(id int,mc text);CREATE TABLEpostgres=#postgres=# CREATE OR REPLACE PROCEDURE p_row (a_row public.t) AS$$BEGINRAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=#CALL p_row(ROW(1,'tdsql_pg'));NOTICE: id = 1 ; mc = tdsql_pgCALLpostgres=#
行数组
postgres=# CREATE OR REPLACE PROCEDURE p_row_array (a_rec public.t[]) AS$$BEGINRAISE NOTICE 'a_rec = %',a_rec;RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_row_array(array[row(1,'tdsql_pg'),row(1,'pgxz')]::public.t[]);NOTICE: a_rec = {"(1,tdsql_pg)","(1,pgxz)"}NOTICE: a_rec[1].id = 1CALLpostgres=#
游标类型
postgres=# CREATE OR REPLACE PROCEDURE p_refcursor (a_ref refcursor) AS$$DECLARE v_rec record;BEGINOPEN a_ref FOR SELECT * FROM t LIMIT 1;FETCH a_ref INTO v_rec;RAISE NOTICE 'v_rec = % ',v_rec;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_refcursor('a');NOTICE: v_rec = (1,tdsql_pg)CALLpostgres=#
多态类型
postgres=# CREATE OR REPLACE PROCEDURE f_any(a_arg anyelement) AS$$BEGINRAISE NOTICE '%',a_arg;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL f_any(1);NOTICE: 1CALLpostgres=# CALL f_any('tdsql_pg'::varchar);NOTICE: tdsql_pgCALLpostgres=#postgres=# SELECT f_any('tdsql_pg'::TEXT);NOTICE: tdsql_pgf_any-------(1 行记录)postgres=# CALL f_any(ROW(1,'tdsql_pg')::public.t);NOTICE: (1,tdsql_pg)CALLpostgres=#postgres=# CALL f_any(ARRAY[1,2]::INTEGER[]);NOTICE: {1,2}CALLpostgres=#postgres=# CALL f_any(ARRAY[[1,2],[3,4],[5,6]]::INTEGER[][][]);NOTICE: {{1,2},{3,4},{5,6}}CALLpostgres=#
注意多态类型参数调用时最好直接声明参数类型,否则有可能出错。
postgres=# CREATE OR REPLACE PROCEDURE f_any(a_arg anyarray) AS$$BEGINRAISE NOTICE '%',a_arg;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# call f_any(ARRAY['tdsql_pg','pgxz']::TEXT[]);ERROR: procedure f_any(text[]) is not uniqueLINE 1: call f_any(ARRAY['tdsql_pg','pgxz']::TEXT[]);^HINT: Could not choose a best candidate procedure. You might need to add explicit type casts.postgres=#
注意
Anyelement 参数如果写成数组,其意义就跟 anyarray 参数一致,所以 f_any(a_arg anyelement) 与 f_any(a_arg anyarray) 在调用 f_any(ARRAY[1,2]) 时就会出现函数不是唯一化的错误 (ERROR: function f_any(…) is not unique) 提示。
参数默认值
postgres=# CREATE OR REPLACE PROCEDURE p_default_value (a_int INTEGER DEFAULT 1) AS$$BEGINRAISE NOTICE 'a_int = %',a_int;END;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# CALL p_default_value(2);NOTICE: a_int = 2CALLpostgres=# CALL p_default_value();NOTICE: a_int = 1CALLpostgres=#
说明
如果原来存在一个 p_default_value () 这样的存储过程,则上面的执行就会出错,因为系统无法清楚到底要执行哪个函数,如下所示。
postgres=# CREATE OR REPLACE PROCEDURE p_default_value() AS$$BEGINRAISE NOTICE '无参数';END;$$LANGUAGE plpgsql ;CREATE PROCEDUREpostgres=# CALL p_default_value();ERROR: procedure p_default_value() is not uniqueLINE 1: CALL p_default_value();^HINT: Could not choose a best candidate procedure. You might need to add explicit type casts.postgres=#
出错提示,p_default_value () 存储过程不是唯一的,这是使用上一个需要特别注意的地方。