《PostgreSQL 开发指南》第 26 篇存储过程

不剪发的Tony老师

《SQL编程思想》作者，PostgreSQL ACE。

为了方便大家阅读学习，制作了专栏的电子版PDF，免费开放下载： https:// github.com/dongxuyang19 85/postgresql_dev_guide

概述

在 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 := 'http://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
    other-statements
END IF;

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

DO $$
BEGIN 
  IF 2 > 3 THEN
    RAISE NOTICE '2 大于 3';
    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';
    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';
      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 
    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';
      RAISE NOTICE 'other value';
  END CASE;
END $$;

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

循环语句

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

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

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

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

DO $$
DECLARE
  i integer := 0;
BEGIN 
    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 
    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]])
    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]])
    RAISE NOTICE 'row = %', x;
  END LOOP;
END $$;
NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}

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

游标

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

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

声明游标变量；
打开游标；
从游标中获取结果；
判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 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);
    -- 获取游标中的记录
    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;
            ROLLBACK;
        END IF;
    END LOOP;
CALL transaction_test();
select * from test;