《PostgreSQL 开发指南》第 26 篇 存储过程数据库Tony.Dong的专栏-

概述

在 PostgreSQL 中，除了标准 SQL 语句之外还支持使用各种过程语言（例如 PL/pgSQL、C、PL/Tcl、PL/Python、PL/Perl、PL/Java 等 ）创建复杂的过程和函数，称为存储过程（Stored Procedure）和自定义函数（User-Defined Function）。存储过程支持许多过程元素，例如控制结构、循环和复杂的计算。

使用存储过程带来的好处包括：

• 减少应用和数据库之间的网络传输。所有的 SQL 语句都存储在数据库服务器中，应用程序只需要发送函数调用并获取除了结果，避免了发送多个 SQL 语句并等待结果。
• 提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。
• 可重用性。存储过程和函数的功能可以被多个应用同时使用。

当然，使用存储过程也可能带来一些问题：

• 导致软件开发缓慢。因为存储过程需要单独学习，而且很多开发人员并不具备这种技能。
• 不易进行版本管理和代码调试。
• 不同数据库管理系统之间无法移植，语法存在较大的差异。

本文主要介绍 PL/pgSQL 存储过程，它和 Oracle PL/SQL 非常类似，是 PostgreSQL默认支持的存储过程。使用 PL/pgSQL 的原因包括：

• PL/pgSQL 简单易学，无论是否具有编程基础都能够很快学会。
• PL/pgSQL 是 PostgreSQL 默认支持的过程语言，PL/pgSQL 开发的自定义函数可以和内置函数一样使用。
• PL/pgSQL 提高了许多强大的功能，例如游标，可以实现复杂的函数。

PL/pgSQL 代码块结构

PL/pgSQL 是一种块状语言，因此存储过程和函数以代码块的形式进行组织。以下是一个 PL/pgSQL 代码块的定义：

[ <<label>> ] [ DECLARE     declarations ] BEGIN     statements; ... END [ label ]; 

其中，label 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定变量的名称；DECLARE 是一个可选的声明部分，用于定义变量；BEGIN 和 END 之间是代码主体，也就是主要的功能代码；所有的语句都使用分号（;）结束，END 之后的分号表示代码块结束。

以下是一个简单的代码块示例：

DO $$  DECLARE   name text; BEGIN    name := 'PL/pgSQL';   RAISE NOTICE 'Hello %!', name; END $$; 

以上是一个匿名块，与此相对的是命名块（也就是存储过程和函数）。其中，DO 语句用于执行匿名块；我们定义了一个字符串变量 name，然后给它赋值并输出一个信息；RAISE NOTICE 用于输出通知消息。

$$ 用于替换单引号（’），因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。对于上面的示例，需要写成以下形式：

DO 'DECLARE   name text; BEGIN    name := ''PL/pgSQL'';   RAISE NOTICE ''Hello %!'', name; END '; 

显然这种写法很不方便，因此 PL/pgSQL 提供了 $$ 避免单引号问题。我们经常还会遇到其他形式的符号，例如 $function$ 或者 $procedure$，作用也是一样。

在 psql 客户端运行以上代码的结果如下：

postgres=# DO $$  postgres$# DECLARE postgres$#   name text; postgres$# BEGIN  postgres$#   name := 'PL/pgSQL'; postgres$#   RAISE NOTICE 'Hello %!', name; postgres$# END $$;  NOTICE:  Hello PL/pgSQL! 

嵌套子块

PL/pgSQL 支持代码块的嵌套，也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代码块被称为子块（subblock），包含子块的代码块被称为外部块（subblock ）。子块可以将代码进行逻辑上的拆分，子块中可以定义与外部块重名的变量，而且在子块内拥有更高的优先级。例如：

DO $$  <<outer_block>> DECLARE   name text; BEGIN    name := 'outer_block';   RAISE NOTICE 'This is %', name; DECLARE       name text := 'sub_block'; BEGIN       RAISE NOTICE 'This is %', name;      RAISE NOTICE 'The name from the outer block is %', outer_block.name; END;    RAISE NOTICE 'This is %', name; END outer_block $$; 

首先，外部块中定义了一个变量 name，值为“outer_block”，输出该变量的值；然后在子块中定义了同名的变量，值为“sub_block”，输出该变量的值，并且通过代码块标签输出了外部块的变量值；最后再次输出该变量的值。以上代码执行的输出结果如下：

NOTICE:  This is outer_block NOTICE:  This is sub_block NOTICE:  The name from the outer block is outer_block NOTICE:  This is outer_block 

声明与赋值

与其他编程语言类似，PL/pgSQL 支持定义变量和常量。

变量

变量是一个有意义的名字，代表了内存中的某个位置。变量总是属于某个数据类型，变量的值可以在运行时被修改。

在使用变量之前，需要在代码的声明部分进行声明：

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ]; 

其中，variable_name 是变量的名称，通常需要指定一个有意义的名称；data_type 是变量的类型，可以是任何 SQL 数据类型；如果指定了 NOT NULL，必须使用后面的表达式为变量指定初始值。

以下是一些变量声明的示例：

user_id integer; quantity numeric(5) DEFAULT 0; url varchar := 'https://mysite.com'; 

除了基本的 SQL 数据类型之外，PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量：

myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; amount quantity%TYPE; 

myrow 是一个行类型的变量，可以存储查询语句返回的数据行（数据行的结构要和 tablename 相同）；myfield 的数据类型取决于 tablename.columnname 字段的定义；amount 和 quantity 的类型一致。

与行类型变量类似的还有记录类型变量，例如：

arow RECORD; 

记录类型的变量没有预定义的结构，只有当变量被赋值时才确定，而且可以在运行时被改变。记录类型的变量可以用于任意查询语句或者 FOR 循环变量。

除此之外，PL/pgSQL 还可以使用 ALIAS 定义一个变量别名：

newname ALIAS FOR oldname; 

此时，newname 和 oldname 代表了相同的对象。

常量

如果在定义变量时指定了 CONSTANT 关键字，意味着定义的是常量。常量的值需要在声明时初始化，并且不能修改。

以下示例通过定义常量 PI 计算圆的面积：

DO $$  DECLARE   PI CONSTANT NUMERIC := 3.14159265;   radius NUMERIC; BEGIN     radius := 1.0;    RAISE NOTICE 'The area is %', PI * radius * radius; END $$;  NOTICE:  The area is 3.1415926500 

常量可以用于避免魔数（magic number），提高代码的可读性；也可以减少代码的维护工作，所有使用常量的代码都会随着常量值的修改而同步，不需要修改多个硬编码的数据值。

控制结构

IF 语句

IF 语句可以基于条件选择性执行操作， PL/pgSQL 提供了三种形式的 IF 语句。

• IF … THEN … END IF
• IF … THEN … ELSE … END IF
• IF … THEN … ELSIF … THEN … ELSE … END IF

首先，最简单的 IF 语句如下：

IF boolean-expression THEN     statements END IF; 

如果表达式 boolean-expression 的值为真，执行 THEN 之后的语句；否则，忽略这些语句。例如：

DO $$ BEGIN IF 2 > 3 THEN  RAISE NOTICE '2 大于 3'; END IF; IF 2 < 3 THEN  RAISE NOTICE '2 小于 3'; END IF; END $$;  NOTICE:  2 小于 3 

第二种 IF 语句的语法如下：

IF boolean-expression THEN     statements ELSE     other-statements END IF; 

如果表达式 boolean-expression 的值为真，执行 THEN 之后的语句；否则，执行 ELSE 之后的语句。例如：

DO $$ BEGIN IF 2 > 3 THEN  RAISE NOTICE '2 大于 3'; ELSE  RAISE NOTICE '2 小于 3'; END IF; END $$;  NOTICE:  2 小于 3 

第三种 IF 语句支持多个条件分支：

IF boolean-expression THEN     statements [ ELSIF boolean-expression THEN     statements ] [ ELSIF boolean-expression THEN     statements ] ... [ ELSE     statements ] END IF; 

依次判断条件中的表达式，如果某个条件为真，执行相应的语句；如果所有条件都为假，执行 ELSE 后面的语句；如果没有 ELSE 就什么都不执行。例如：

DO $$ DECLARE   i integer := 3;   j integer := 3; BEGIN IF i > j THEN  RAISE NOTICE 'i 大于 j';   ELSIF i < j THEN     RAISE NOTICE 'i 小于 j'; ELSE     RAISE NOTICE 'i 等于 j'; END IF; END $$;  NOTICE:  i 等于 j DO 

CASE 语句

除了 IF 语句之外，PostgreSQL 还提供了 CASE 语句，同样可以根据不同的条件执行不同的分支语句。CASE 语句分为两种：简单 CASE 和搜索 CASE 语句。

⚠️CASE 语句和第 15 篇中介绍的 CASE 表达式不是一个概念，CASE 表达式是一个 SQL 表达式。

简单 CASE 语句的结构如下：

CASE search-expression     WHEN expression [, expression [ ... ]] THEN       statements   [ WHEN expression [, expression [ ... ]] THEN       statements     ... ] [ ELSE       statements ] END CASE; 

首先，计算 search-expression 的值；然后依次和 WHEN 中的表达式进行等值比较；如果找到了相等的值，执行相应的 statements；后续的分支不再进行判断；如果没有匹配的值，执行 ELSE 语句；如果此时没有 ELSE，将会抛出 CASE_NOT_FOUND 异常。

例如：

DO $$ DECLARE   i integer := 3; BEGIN CASE i     WHEN 1, 2 THEN       RAISE NOTICE 'one or two'; WHEN 3, 4 THEN       RAISE NOTICE 'three or four'; ELSE       RAISE NOTICE 'other value'; END CASE; END $$;  NOTICE:  three or four 

简单 CASE 语句只能进行简单的等值比较，搜索 CASE 语句可以实现更复杂的控制逻辑：

CASE WHEN boolean-expression THEN       statements   [ WHEN boolean-expression THEN       statements     ... ] [ ELSE       statements ] END CASE; 

依次判断每个 WHEN 之后的表达式，如果为真则执行相应的语句；后续的分支不再进行判断；如果没有匹配的值，执行 ELSE 语句；如果此时没有 ELSE，将会抛出 CASE_NOT_FOUND 异常。例如：

DO $$ DECLARE   i integer := 3; BEGIN CASE WHEN i BETWEEN 0 AND 10 THEN       RAISE NOTICE 'value is between zero and ten'; WHEN i BETWEEN 11 AND 20 THEN       RAISE NOTICE 'value is between eleven and twenty'; ELSE       RAISE NOTICE 'other value'; END CASE; END $$; 

搜索 CASE 表达式可以构造任意复杂的判断逻辑，实现 IF 语句的各种功能。

循环语句

PostgreSQL 提供了 4 种循环执行命令的语句：LOOP、WHILE、FOR 和 FOREACH 循环，以及循环控制的 EXIT 和 CONTINUE 语句。

首先，LOOP 用于定义一个无限循环语句：

[ <<label>> ] LOOP     statements END LOOP [ label ]; 

一般需要使用 EXIT 或者 RETURN 语句退出循环，label 可以用于 EXIT 或者 CONTINUE 语句退出或者跳到执行的嵌套循环中。例如：

DO $$ DECLARE   i integer := 0; BEGIN LOOP EXIT WHEN i = 5;     i := i + 1;     RAISE NOTICE 'Loop: %', i; END LOOP; END $$;  NOTICE:  Loop: 1 NOTICE:  Loop: 2 NOTICE:  Loop: 3 NOTICE:  Loop: 4 NOTICE:  Loop: 5 

其中，EXIT 语句用于退出循环。完整的 EXIT 语句如下：

EXIT [ label ] [ WHEN boolean-expression ]; 

另一个控制循环的语句是 CONTINUE：

CONTINUE [ label ] [ WHEN boolean-expression ]; 

CONTINUE 表示忽略后面的语句，直接进入下一次循环。例如：

DO $$ DECLARE   i integer := 0; BEGIN LOOP EXIT WHEN i = 10;     i := i + 1; CONTINUE WHEN mod(i, 2) = 1;     RAISE NOTICE 'Loop: %', i; END LOOP; END $$;  NOTICE:  Loop: 2 NOTICE:  Loop: 4 NOTICE:  Loop: 6 NOTICE:  Loop: 8 NOTICE:  Loop: 10 

当变量 i 为奇数时，直接进入下一次循环，不会打印出变量的值。

WHILE 循环的语法如下：

[ <<label>> ] WHILE boolean-expression LOOP     statements END LOOP [ label ]; 

当表达式 boolean-expression 的值为真时，循环执行其中的语句；然后重新计算表达式的值，当表达式的值假时退出循环。例如：

DO $$ DECLARE   i integer := 0; BEGIN WHILE i < 5 LOOP     i := i + 1;     RAISE NOTICE 'Loop: %', i; END LOOP; END $$;  NOTICE:  Loop: 1 NOTICE:  Loop: 2 NOTICE:  Loop: 3 NOTICE:  Loop: 4 NOTICE:  Loop: 5 

FOR 循环可以用于遍历一个整数范围或者查询结果集，遍历整数范围的语法如下：

[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP     statements END LOOP [ label ]; 

FOR 循环默认从小到大进行遍历，REVERSE 表示从大到小遍历；BY 用于指定每次的增量，默认为 1。例如：

DO $$ BEGIN FOR i IN 1..5 BY 2 LOOP  RAISE NOTICE 'Loop: %', i; END LOOP; END $$;  NOTICE:  Loop: 1 NOTICE:  Loop: 3 NOTICE:  Loop: 5 

变量 i 不需要提前定义，可以在 FOR 循环内部使用。

遍历查询结果集的 FOR 循环如下：

[ <<label>> ] FOR target IN query LOOP     statements END LOOP [ label ]; 

其中，target 可以是一个 RECORD 变量、行变量或者逗号分隔的标量列表。在循环中，target 代表了每次遍历的行数据。例如：

DO $$ DECLARE   emp record; BEGIN FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP  RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name; END LOOP; END $$;  NOTICE:  Loop: Steven,King NOTICE:  Loop: Neena,Kochhar NOTICE:  Loop: Lex,De Haan NOTICE:  Loop: Alexander,Hunold NOTICE:  Loop: Bruce,Ernst 

FOREACH 循环与 FOR 循环类似，只不过变量的是一个数组：

[ <<label>> ] FOREACH target [ SLICE number ] IN ARRAY expression LOOP     statements END LOOP [ label ]; 

如果没有指定 SLICE 或者指定 SLICE 0，FOREACH 将会变量数组中的每个元素。例如：

DO $$ DECLARE   x int; BEGIN   FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]]) LOOP     RAISE NOTICE 'x = %', x; END LOOP; END $$;  NOTICE:  x = 1 NOTICE:  x = 2 NOTICE:  x = 3 NOTICE:  x = 4 NOTICE:  x = 5 NOTICE:  x = 6 

如果指定了一个正整数的 SLICE，FOREACH 将会变量数组的切片；SLICE 不能大于数组的维度。例如：

DO $$ DECLARE   x int[]; BEGIN   FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]]) LOOP     RAISE NOTICE 'row = %', x; END LOOP; END $$;  NOTICE:  row = {1,2,3} NOTICE:  row = {4,5,6} 

以上示例通过 FOREACH 语句遍历了数组的一维切片。

游标

PL/pgSQL 游标允许我们封装一个查询，然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录，避免内存溢出；另外，我们可以定义一个返回游标引用的函数，然后调用程序可以基于这个引用处理返回的结果集。

使用游标的步骤大体如下：

1. 声明游标变量；
2. 打开游标；
3. 从游标中获取结果；
4. 判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 5 步；
5. 关闭游标。

我们直接通过一个示例演示使用游标的过程：

DO $$ DECLARE    rec_emp RECORD;   cur_emp CURSOR(p_deptid INTEGER) FOR SELECT first_name, last_name, hire_date      FROM employees  WHERE department_id = p_deptid; BEGIN -- 打开游标 OPEN cur_emp(60); LOOP -- 获取游标中的记录 FETCH cur_emp INTO rec_emp; -- 没有找到更多数据时退出循环 EXIT WHEN NOT FOUND;      RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date; END LOOP; -- Close the cursor CLOSE cur_emp; END $$;  NOTICE:  Alexander,Hunold hired at:2006-01-03 NOTICE:  Bruce,Ernst hired at:2007-05-21 NOTICE:  David,Austin hired at:2005-06-25 NOTICE:  Valli,Pataballa hired at:2006-02-05 NOTICE:  Diana,Lorentz hired at:2007-02-07 

首先，声明了一个游标 cur_emp，并且绑定了一个查询语句，通过一个参数 p_deptid 获取指定部门的员工；然后使用 OPEN 打开游标；接着在循环中使用 FETCH 语句获取游标中的记录，如果没有找到更多数据退出循环语句；变量 rec_emp 用于存储游标中的记录；最后使用 CLOSE 语句关闭游标，释放资源。

游标是 PL/pgSQL 中的一个强大的数据处理功能，更多的使用方法可以参考官方文档。

错误处理

报告错误和信息

PL/pgSQL 提供了 RAISE 语句，用于打印消息或者抛出错误：

RAISE level format; 

不同的 level 代表了错误的不同严重级别，包括：

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION

在上文示例中，我们经常使用 NOTICE 输出一些信息。如果不指定 level，默认为 EXCEPTION，将会抛出异常并且终止代码运行。

format 是一个用于提供信息内容的字符串，可以使用百分号（%）占位符接收参数的值， 两个连写的百分号（%%）表示输出百分号自身。

以下是一些 RAISE 示例：

DO $$  BEGIN    RAISE DEBUG 'This is a debug text.';   RAISE INFO 'This is an information.';   RAISE LOG 'This is a log.';   RAISE WARNING 'This is a warning at %', now();   RAISE NOTICE 'This is a notice %%'; END $$;  INFO:  This is an information. WARNING:  This is a warning at 2020-05-16 11:27:06.138569+08 NOTICE:  This is a notice % 

从结果可以看出，并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参数 client_min_messages 和 log_min_messages 进行设置。

对于 EXCEPTION 级别的错误，可以支持额外的选项：

RAISE [ EXCEPTION ] format USING option = expression [, ... ]; RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ]; RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ]; RAISE [ EXCEPTION ] USING option = expression [, ... ]; 

其中，option 可以是以下选项：

• MESSAGE，设置错误消息。如果 RAISE 语句中已经包含了 format 字符串，不能再使用该选项。
• DETAIL，指定错误详细信息。
• HINT，设置一个提示信息。
• ERRCODE，指定一个错误码（SQLSTATE）。可以是文档中的条件名称或者五个字符组成的 SQLSTATE 代码。
• COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相关对象的名称。

以下是一些示例：

RAISE EXCEPTION 'Nonexistent ID --> %', user_id       USING HINT = 'Please check your user ID';  RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';  RAISE division_by_zero; RAISE SQLSTATE '22012'; 

检查断言

PL/pgSQL 提供了 ASSERT 语句，用于调试存储过程和函数：

ASSERT condition [ , message ]; 

其中，condition 是一个布尔表达式；如果它的结果为真，ASSERT 通过；如果结果为假或者 NULL，将会抛出 ASSERT_FAILURE 异常。message 用于提供额外的错误信息，默认为“assertion failed”。例如：

DO $$  DECLARE    i integer := 1; BEGIN    ASSERT i = 0, 'i 的初始值应该为 0！'; END $$;  ERROR:  i 的初始值应该为 0！ CONTEXT:  PL/pgSQL function inline_code_block line 5 at ASSERT 

⚠️注意，ASSERT 只适用于代码调试；输出错误信息使用 RAISE 语句。

捕获异常

默认情况下，PL/pgSQL 遇到错误时会终止代码执行，同时撤销事务。我们也可以在代码块中使用 EXCEPTION 捕获错误并继续事务：

[ <<label>> ] [ DECLARE     declarations ] BEGIN     statements EXCEPTION     WHEN condition [ OR condition ... ] THEN         handler_statements     [ WHEN condition [ OR condition ... ] THEN           handler_statements       ... ] END; 

如果代码执行出错，程序将会进入 EXCEPTION 模块；依次匹配 condition，找到第一个匹配的分支并执行相应的 handler_statements；如果没有找到任何匹配的分支，继续抛出错误。

以下是一个除零错误的示例：

DO $$  DECLARE    i integer := 1; BEGIN    i := i / 0; EXCEPTION   WHEN division_by_zero THEN     RAISE NOTICE '除零错误！'; WHEN OTHERS THEN     RAISE NOTICE '其他错误！'; END $$;  NOTICE:  除零错误！ 

OTHERS 用于捕获未指定的错误类型。

PL/pgSQL 还提供了捕获详细错误信息的 GET STACKED DIAGNOSTICS 语句，具体可以参考官方文档。

自定义函数

要创建一个自定义的 PL/pgSQL 函数，可以使用 CREATE FUNCTION 语句：

CREATE [ OR REPLACE ] FUNCTION   name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) RETURNS rettype AS $$ DECLARE   declarations BEGIN   statements; ... END; $$ LANGUAGE plpgsql; 

CREATE 表示创建函数，OR REPLACE 表示替换函数定义；name 是函数名；括号内是参数，多个参数使用逗号分隔；argmode 可以是 IN（输入）、OUT（输出）、INOUT（输入输出）或者 VARIADIC（数量可变），默认为 IN；argname 是参数名称；argtype 是参数的类型；default_expr 是参数的默认值；rettype 是返回数据的类型；AS 后面是函数的定义，和上文中的匿名块相同；最后，LANGUAGE 指定函数实现的语言，也可以是其他过程语言。

以下示例创建一个函数 get_emp_count，用于返回指定部门中的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer) RETURNS integer AS $$ DECLARE   ln_count integer; BEGIN select count(*) into ln_count   from employees   where department_id = p_deptid; return ln_count; END; $$ LANGUAGE plpgsql; 

创建该函数之后，可以像内置函数一样在 SQL 语句中进行调用：

select department_id,department_name,get_emp_count(department_id) from departments d; department_id|department_name     |get_emp_count| -------------|--------------------|-------------| 10|Administration      | 1| 20|Marketing           | 2| 30|Purchasing          | 6| ... 

PL/pgSQL 函数支持重载（Overloading），也就是相同的函数名具有不同的函数参数。例如，以下语句创建一个重载的函数 get_emp_count，返回指定部门指定日期之后入职的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date) RETURNS integer AS $$ DECLARE   ln_count integer; BEGIN select count(*) into ln_count   from employees   where department_id = p_deptid and hire_date >= p_hiredate; return ln_count; END; $$ LANGUAGE plpgsql; 

查询每个部门 2005 年之后入职的员工数量：

select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01') from departments d; department_id|department_name     |get_emp_count|get_emp_count| -------------|--------------------|-------------|-------------| 10|Administration      | 1| 0| 20|Marketing           | 2| 1| 30|Purchasing          | 6| 4| ... 

我们再来看一个 VARIADIC 参数的示例：

CREATE OR REPLACE FUNCTION sum_num(   VARIADIC nums numeric[]) RETURNS numeric AS $$ DECLARE ln_total numeric; BEGIN SELECT SUM(nums[i]) INTO ln_total   FROM generate_subscripts(nums, 1) t(i); RETURN ln_total; END; $$ LANGUAGE plpgsql; 

参数 nums 是一个数组，可以传入任意多个参数；然后计算它们的和值。例如：

SELECT sum_num(1,2), sum_num(1,2,3); sum_num|sum_num| -------|-------| 3| 6| 

如果函数不需要返回结果，可以返回 void 类型；或者直接使用存储过程。

存储过程

PostgreSQL 11 增加了存储过程，使用 CREATE PROCEDURE 语句创建：

CREATE [ OR REPLACE ] PROCEDURE   name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) AS $$ DECLARE   declarations BEGIN   statements; ... END; $$ LANGUAGE plpgsql; 

存储过程的定义和函数主要的区别在于没有返回值，其他内容都类似。以下示例创建了一个存储过程 update_emp，用于修改员工的信息：

CREATE OR REPLACE PROCEDURE update_emp(   p_empid in integer,   p_salary in numeric,   p_phone in varchar) AS $$ BEGIN update employees    set salary = p_salary,       phone_number = p_phone   where employee_id = p_empid; END; $$ LANGUAGE plpgsql; 

调用存储过程使用 CALL 语句：

call update_emp(100, 25000, '515.123.4560'); 

事务管理

在存储过程内部，可以使用 COMMIT 或者 ROLLBACK 语句提交或者回滚事务。例如：

create table test(a int); CREATE PROCEDURE transaction_test() LANGUAGE plpgsql AS $$ BEGIN FOR i IN 0..9 LOOP INSERT INTO test (a) VALUES (i); IF i % 2 = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END $$; CALL transaction_test(); select * from test; a| -| 0| 2| 4| 6| 8| 

只有偶数才会被最终提交。

欢迎👍、评论📝、❤️！