Chapter 36. PL/pgSQL - SQL 过程语言

Table of Contents
36.1. 概述
36.1.1. 使用PL/pgSQL的优点
36.1.2. 所支持的参数和结果数据类型
36.2. 开发 PL/pgSQL 的一些提示
36.3. PL/pgSQL的结构
36.4. 声明
36.4.1. 函数参数的别名
36.4.2. 拷贝类型
36.4.3. 行类型
36.4.4. 记录类型
36.4.5. RENAME
36.5. 表达式
36.6. 基本语句
36.6.1. 赋值
36.6.2. SELECT INTO
36.6.3. 执行一个没有结果的表达式或者命令
36.6.4. 执行动态命令
36.6.5. 获取结果状态
36.7. 控制结构
36.7.1. 从函数返回
36.7.2. 条件
36.7.3. 简单循环
36.7.4. 遍历命令结果
36.7.5. 捕获错误
36.8. 游标
36.8.1. 声明游标变量
36.8.2. 打开游标
36.8.3. 使用游标
36.9. 错误和消息
36.10. 触发器过程
36.11. Oracle的 PL/SQL 移植
36.11.1. 移植样例
36.11.2. 其它要注意的东西
36.11.3. 附录

PL/pgSQLPostgreSQL 数据库系统的一个可装载的过程语言。 PL/pgSQL的设计目标是创建一种可装载的过程语言,可以

除了用于用户定义类型的输入/输出转换和计算函数以外, 任何可以在 C 语言函数里定义的东西都可以在 PL/pgSQL里使用。 比如,我们可以创建复杂的条件计算函数, 并随后将之用于定义操作符或者用于函数索引中。

36.1. 概述

PL/pgSQL 函数第一次(在任何一个服务器进程内部)被调用时, PL/pgSQL 的调用句柄分析函数源文本生成二进制指令树。 该指令树完全转换了 PL/pgSQL 语句结构, 但是在函数内使用到的独立的 SQL 表达式和 SQL 命令并未立即转换。

在每个函数中用到的表达式和 SQL 命令在函数里首次使用的时候, PL/pgSQL 解释器创建一个准备好的执行规划(使用 SPI 管理器的 SPI_prepareSPI_saveplan 函数)。 随后对该表达式或者命令的访问都将使用已准备好的规划。 因此,一个在条件代码中有许多语句,可能需要执行规划的函数, 只需要准备和保存那些真正在数据库联接期间真正使用到的规划。 这样可以有效地减少为 PL/pgSQL 函数里的语句生成分析和执行规划的总时间。 不过有个缺点是在特定表达式或者命令中的错误可能要到函数中的那部分执行到的时候才能发现。

一旦 PL/pgSQL 在函数里为一个命令制定了执行计划, 那么它将在该次数据库联接的生命期内复用该规划。 这么做在性能上通常会更好一些,但是如果你动态地修改你的数据库模式,那么就可能有问题。 比如:

CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- 声明段
BEGIN
    PERFORM my_function();
END;
$$ LANGUAGE plpgsql;

如果你执行上面的函数,那么它将在为PERFORM语句生成的执行计划中中引用 my_function() 的 OID。 然后,如果你删除然后重新创建 my_function(), 那么 populate() 就会再也找不到 my_function()。 这时候你只能重新创建 populate(), 或者至少是重新开始一个新的数据库会话,好让该函数能重新编译一次。 另外一个避免这种问题的方法是在更新my_function 的定义的时候 使用 CREATE OR REPLACE FUNCTION (如果一个函数被"替换",那么它的 OID 将不会变化)。

因为Pl/pgSQL用这种方法保存执行规划, 所以那些在PL/pgSQL里直接出现的 SQL 命令必须在每次执行的时候引用相同的表和字段; 也就是说,你不能拿一个参数用做 SQL 命令中的表或者字段的名称。 要绕开这个限制,你可以用 PL/pgSQLEXECUTE语句动态地构造命令 — 代价是每次执行的时候都构造一个新的命令计划。

注意: PL/pgSQLEXECUTE语句和 PostgreSQL 服务器支持的 EXECUTE 语句没有关系。 服务器的EXECUTE语句不能在 PL/pgSQL 函数中使用(而且也没必要)。

36.1.1. 使用PL/pgSQL的优点

SQLPostgreSQL 和大多数其它关系型数据库用做命令语言的语言。 它是可以移植的,并且容易学习使用。 但是所有 SQL 语句都必须由数据库服务器独立地执行。

这就意味着你的客户端应用必须把每条命令发送到数据库服务器, 等待它处理这个命令,接收结果,做一些运算,然后给服务器发送另外一条命令。 所有这些东西都会产生进程间通讯,并且如果你的客户端在另外一台机器上甚至还会导致网络开销。

如果使用了PL/pgSQL,那么你可以把一块运算和一系列命令在数据库服务器里面组成一个块, 这样就拥有了过程语言的力量并且简化 SQL 的使用,因而节约了大量的时间,因为你用不着付出客户端/服务器通讯的过热。 这样可能产生明显的性能提升。

同样,在 PL/pgSQL 里,你可以使用 SQL 的所有数据类型,操作符和函数。

36.1.2. 所支持的参数和结果数据类型

并且它们可以返回这种任何这种类型的数值。它们还可以接受或者返回任意用名字声明的复合类型(行类型)。 我们还可以声明一个 PL/pgSQL 函数为返回 record 的函数, 意思是结果是一个行类型,这个行的字段是在调用它的查询中指定的,就像我们在 Section 7.2.1.4 里讨论的那样。

PL/pgSQL 函数还可以声明为接受并返回多态的类型 anyelementanyarray。 一个多态的函数实际操作的数据类型可以在不同的调用环境中变化, 如我们在 Section 32.2.5 里讨论的那样。 一个例子是 Section 36.4.1

PL/pgSQL 还可以声明为返回一个它们可以返回的任何单个实例的"集(set)",或者表。 这样的函数通过为结果集每个需要返回的元素执行一个 RETURN NEXT 生成它的输出。

最后,PL/pgSQL 函数可以声明为返回 void,如果它没啥有用的东西可以返回的话。

Note: PL/pgSQL 目前还不是完全支持域类型:它看待域类型和下层的标量类型是一样的。 这就意味着与域关联的约束将不会被强制。对于函数参数,这不是什么问题, 但是如果你把 PL/pgSQL 函数声明为返回一个域类型,那么就有危险。

PL/pgSQL 函数也可以声明为输出某种类型的参数,代替明确的返回类型的声明。 这么做并未给该语言增加任何基础设施,只是通常更方便些,特别是返回多行数值的时候。

具体的例子在 Section 36.4.1Section 36.7.1 里。