PostgreSQL

PostgreSQL
	第四十三章. 过程语言

---

PL/pgSQL

PL/pgSQL 是用于 Postgres 数据库系统的可装载的过程语言．

这个软件包最初是 Jan Wieck 写的．

概述

PL/pgSQL 的设计目标是创建一个可装载的过程化语言，该语言可以

• 用于创建函数和触发器过程，
• 向 SQL 语言里面增加控制结构，
• 可以进行复杂的计算，
• 继承所有用户定义类型，函数和操作符，
• 可以定义为服务器信任的语言，
• 易于使用．

PL/pgSQL 调用控制器在函数第一次被后端调用时，分析函数源代码文本并且生成内部的二进制指令树．生成的字节码在调用控制器中是用函数的对象标识（OID）来标识的．这样就保证了用 DROP/CREATE 序列改变一个函数将不用建立一个新的数据库联接就可以生效．

对于函数中用到的所有表达式和 SQL 语句，PL/pgSQL 字节码转换器用 SPI 管理器函数 SPI_prepare() 和 SPI_saveplan() 创建一个准备好的执行规划．这些是在独立的语句第一次被 PL/pgSQL 函数处理时完成的．这样，一个有条件代码的函数（这些条件代码包含许多要求执行规划的语句）将只准备和保存那些在整个数据库联接周期内真正使用到的规划．

除了用于用户定义类型的输入输出转换和计算函数以外，任何可以在 C 语言函数里定义的东西都可以在 PL/pgSQL 里做．我们有可能创建复杂的条件计算函数并把它们用于定义操作符或在功能性索引中使用它们．

描述

PL/pgSQL 的结构

PL/pgSQL 语言是大小写无关的．所有的关键字和标识符都可以混合大小写使用．

PL/pgSQL 是一种面向语句块的语言．一个语句块象下面一样定义

    [<<label>>]

    [DECLARE

        declarations]

    BEGIN

        statements

    END;

在一个块的语句段里可以有任意子语句块．子语句块可以用于隐藏一个语句块的外部变量．在语句块前面的声明段里定义的变量在每次进入语句块时都初始化，而不是每次函数调用时初始化．

我们一定不能把 PL/pgSQL 里的分组语句 BEGIN/END 和用于事务控制的数据库命令搞混了．函数和触发器不能开始或提交一个事务并且Postgres 也没有嵌套事务的用法．

注释

在 PL/pgSQL 里有两种类型的注释．双破折号 '--' 引出到该行行尾的单行注释．一个 '/*' 开始一个块注释，一直延伸到下一个 '*/'出现．块注释不能嵌套使用，但是双破折号注释可以封装在一个块注释里面并且双破折号注释可以隐藏一个块注释分隔符 '/*' 和 '*/'．

声明

在一个语句块里的所有变量，行和记录或者它的自语句块都必须在一个语句块的声明段里定义－－除了 FOR 循环里说明循环范围的循环变量以外．赋予一个 PL/pgSQL 函数的参数自动用常用的标识 $n 定义．定义有下面语法：

name [ CONSTANT ] >type [ NOT NULL ] [ DEFAULT | := value ];

定义一个指明了的基本类型的变量．如果变量被定义为 CONSTANT，该变量值就不能修改．如果声明了 NOT NULL，则赋予一个 NULL 值给该变量将导致一个运行时错误．因为所有变量的缺省值都是 SQLNULL，因而所有定义为 NOT NULL 的变量都必须有指明的缺省值．

缺省值在每次函数调用时都被计算．所以赋予 'now' 给一个类型 datetime 的变量将导致变量拥有实际函数调用时的时间，而不是函数被编译为字节码的时间．

name class%ROWTYPE;

定义一个带有指定表的结构的行．表必须是一个数据库中现存的表或视图的名称．行的字段是通过点定义来访问的．函数的参数可以是复合类型（完整的行）．这时，对应的标识 $n 将是一个行类型（rowtype），但是它必须用下面描述的 ALIAS 命令取个别名．在一行里只有用户字段可以访问，而 OID 或其他系统字段都不可访问（因为行可能来自一个视图，而视图的行并没有有用的系统字段）．

行类型（rowtype）字段继承表中 char() 等数据类型的字段尺寸或精度．

name RECORD;

记录（Records）类似于行类型（rowtypes），但它们没有预定义的结构．它们在选择和 FOR 循环中使用，用于从一个 SELECT 操作中保存一个具体的数据库的行．该条记录可以在不同的选择中使用．当没有实际的行存在于其中时试图访问一条记录或赋予某个变量的值将导致一个运行时错误产生．

触发器里的 NEW 和 OLD 行作为记录传给过程．这一点是必要的，因为 Postgres 里同样的触发器过程可以为不同的表控制触发器事件．

name ALIAS FOR $n;

为了让代码更具可读性，我们可以为函数的位置参数定义一个别名．

当把复合类型做为参数传递给一个函数时要求这个别名．在 SQL 函数里的点表示法（$1.salary）在 PL/pgSQL 里是不允许的．

RENAME oldname TO newname;

改变变量，记录或者行的名称．这一点当 NEW 或 OLD 要被触发器里另一个名字引用时很有用．

数据类型

变量类型可以是任意数据库里现有基本类型．上面声明段里的 type 是这样定义的：

• Postgres-基本类型
• variable%TYPE
• class.field%TYPE

variable 是变量名，事先在同一函数里定义，在这一点上可见.

class 是现存表或视图，field 是一个字段的名称．

使用 class.field%TYPE 导致 PL/pgSQL 在后端的生存期里第一次调用该函数时查看字段的定义．如果我们有一个表有一个字段定义为 char(20) 而且一些 PL/pgSQL 函数在局部变量里处理该字段的内容．现在我们发现 char(20) 不够长，把表倒出来，删除，重新把有问题的字段定义为 char(40) 然后恢复数据．哈－他忘了函数．函数里的运算将把数值裁断成20个字符．但是如果他们用 class.field%TYPE 定义，它们将自动控制字段的长度的变化或新表把该字段定义为 text 类型．

表达式

在 PL/pgSQL 语句里的所有表达式都用后端的执行器进行处理．包含约束的表达式可能实际上需要运行时计算（例如．datetime 类型的'now'），所以对于 PL/pgSQL 分析器而言，除了 NULL 关键字以外它是不可能识别真正常量的值的．所有表达式都通过用 SPI 管理器在内部运行查询

    SELECT expression

   

来计算，在表达式里出现的变量标识都被从参数数组传入执行器的实际变量代替．所有在 PL/pgSQL 函数里用到的表达式都只准备和存储一次．

Postgres 的主分析器做的类型检查对转换常量有一些副作用．详细说来，下面的两个函数有一些区别

    CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '

        DECLARE

            logtxt ALIAS FOR $1;

        BEGIN

            INSERT INTO logtable VALUES (logtxt, ''now'');

            RETURN ''now'';

        END;

    ' LANGUAGE 'plpgsql';

   

和

    CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '

        DECLARE

            logtxt ALIAS FOR $1;

            curtime datetime;

        BEGIN

            curtime := ''now'';

            INSERT INTO logtable VALUES (logtxt, curtime);

            RETURN curtime;

        END;

    ' LANGUAGE 'plpgsql';

   

在 logfunc1() 里，Postgres 的主分析器在准备 INSERT 的规划时认为，字符串 'now' 应该解释为 datetime，因为 logtable 的目标字段是该类型．因此，这时它将从中生成一个常量并且在所有后端生存期内所有对 logfunc1() 的调用时使用该常量．不用说这可不是程序员希望的．

在 logfunc2() 里，Postgres 的主分析器并不知道 'now' 应该转换的类型，因此它返回一个包含字符串 'now' 的 text 数据类型．在给局部变量 curtime 赋值时，PL/pgSQL 解释器通过调用 text_out()和 datetime_in() 把这个字符串转换成 datetime 类型的变量．

这个 Postgres 主分析器的类型检查是在 PL/pgSQL 接近完成的时候实现的．在版本 6.3 和版本 6.4 之间有所不同并且影响所有使用 SPI 管理器的规划特性的函数．使用上面提到的局部变量的方法是目前能让 PL/pgSQL 对那些数值正确解释的唯一方法．

如果在表达式或语句里用到记录字段，字段的数据类型在调用中不应该在同样的表达式中改变．如果书写控制多个表的触发器过程时应该注意这一点．

语句

任何象下面声明的 PL/pgSQL 分析器不能理解的东西将被放到查询里并发送给数据库引擎执行．生成的查询应该不返回任何数据．

赋值

给一个变量或行/记录赋值用下面方法

    identifier := expression;

   

如果表达式的结果数据类型和变量数据类型不一致，或者变量具有已知的尺寸/精度（例如 char(20)），结果值将隐含的被 PL/pgSQL 字节码解释器用结果类型的输出和变量类型的输入函数转换．要注意这样做可能潜在地导致类型输入函数生成的运行时错误．

下面方法可以把一个完整的选择放到一条记录或者行里

    SELECT expressions INTO target FROM ...;

   

target 可以是一条记录，一行变量或一个逗号分隔的变量列表和记录/行字段（域）．

如果一行或者一个变量列表当做目标，选择的数值必须与目标列的结构完全一样，否则会产生一条运行时错误．FORM 关键字可以跟随着任意可在 SELECT 语句里使用的有效的资格（条件），分组，排序等．

一个名为 FOUND 的特殊的布尔类型的变量可以在 SELECT INTO 之后立刻用于检查赋值是否成功．

    SELECT * INTO myrec FROM EMP WHERE empname = myname;

    IF NOT FOUND THEN

        RAISE EXCEPTION ''employee % not found'', myname;

    END IF;

   

如果选择返回多行，只有第一行被放到目标域里面．其他所有都被悄悄地丢弃掉了．

调用其他函数

所有在 Prostgres 数据库里的函数返回一个值．因此，通常的调用函数的方法是执行一条 SELECT 查询或者做一个赋值（导致一个 PL/pgSQL 的内部 SELECT ）．但是有时候我们对函数结果并不感兴趣．

    PERFORM query

   

将在 SPI 管理器上执行一个 'SELECT query' 然后丢弃结果．象局部变量这样的标识仍然代入参数．

从函数返回

    RETURN expression

   

函数结束并且表达式 expression 的值将返回给上层执行器．函数的返回值不能取消定义．如果控制到达一个函数体的最顶层而没有碰到一个 RETURN 语句，将产生一个运行时错误．

表达式的结果将被自动转换成函数返回类型－－象我们在赋值里描述的那样．

退出和消息

象上面的例子指明的那样，有一个 RAISE 语句可以丢出一条信息到 Postgres elog 机制里面．

    RAISE level

   ''format'' [,

    identifier [...]];

   

在格式 （ format ）里面，“%” 用做一个后面逗号分隔的标识的替换．可能的级别是 DEBUG （安静地停止运行的数据库），NOTICE （向数据库书写日志并向前端客户应用发送信息）和 EXCEPTION （向数据库书写日志并且推出事务）．

条件

    IF expression THEN

        statements

    [ELSE

        statements]

    END IF;

   

表达式 expression 必须返回一个最起码可以转换成布尔类型的数值．

循环

有许多循环的类型．

    [<<label>>]

    LOOP

        statements

    END LOOP;

   

一个无条件循环必须用一条 EXIT 语句显式终止．可选的标记 label 可以被 EXIT 用于嵌套循环用于声明应该结束哪一层循环．

    [<<label>>]

    WHILE expression LOOP

        statements

    END LOOP;

   

一个条件循环在表达式 expression 为真时执行．

    [<<label>>]

    FOR name IN [ REVERSE ]

expression .. expression LOOP

        statements

    END LOOP;

   

一个在某一整数范围内执行的循环，变量 name 自动做为整数类型创建，并且只存在于循环内部．两个表达式给出循环范围的区间，它们只是在进入循环后才被计算出数值．循环步进值总是 1．

    [<<label>>]

    FOR record | row IN select_clause LOOP

        statements

    END LOOP;

   

记录或行被赋予了 select 子句来的所有行，并且语句 statement 对每一条记录/行都运行一次．如果用 EXIT 语句退出循环，最后赋值的行仍然在循环后可以被访问．

    EXIT [ label ] [ WHEN expression ];

   

如果没有标记 label ，最内层的循环将被结束并且 END LOOP 后面的语句将被接着执行．如果给出了标记 label ，该标记必须是当前或者上层嵌套的循环块．那么命名的循环或者语句块被终止并且控制落到循环/语句块的对应 END 的后面一条语句．

触发器过程

PL/pgSQL 可以用于定义触发器过程．它们通过通常的 CREATE FUNCTION 命令创建为没有参数并且返回 OPAQUE 类型的函数．

做为触发器过程的函数有一些 Postgres 特有的细节说明．

首先他们有一些在顶层的声明段里自动定义的特殊变量．有如下这些

NEW

数据类型是 RECORD；该变量保存着行（ROW）一级的触发器在 INSERT/UPDATE 操作时的新的数据库行．

OLD

数据类型是 RECORD；该变量保存着行（ROW）一级的触发器在 INSERT/UPDATE 操作时的旧的数据库行。

TG_NAME

数据类型是 name；该变量包含实际触发的触发器名．

TG_WHEN

数据类型是 text；是一个由触发器定义决定的字符串，要么是 'BEFORE' 要么是 'AFTER'．

TG_LEVEL

数据类型是 text；是一个由触发器定义决定的字符串，要么是 'ROW' 要么是 'STATEMENT'．

TG_OP

数据类型是 text；是一个说明触发器实际进行的操作的字符串，可以是 'INSERT'，'UPDATE' 或者 'DELETE'．

TG_RELID

数据类型是 oid；是导致触发器调用的表的对象标识（OID）．

TG_RELNAME

数据类型是 name；是激活触发器调用的表的名称．

TG_NARGS

数据类型是 integer；是在 CREATE TRIGGER 语句里面赋予触发器过程的参数的个数．

TG_ARGV[]

数据类型是 text 的数组；是 CREATE TRIGGER 语句里的参数．下标从 0 开始记数，并且可以由一个表达式来表示．非法下标（< 0 或 >= tg_nargs）导致一个 NULL 值的返回．

其次，它们必须返回 NULL 或者是一个与导致触发器运行的表的记录/行完全一样的结构的数据．AFTER 类型的触发器可以总是返回一个没有意义的 NULL 值．BEFORE 类的触发器如果返回一个 NULL，将发送一个信号给触发器管理器忽略对实际行的操作．否则，返回的记录/行将代替插入/更新操作中的行．我们可能用一个值直接代替 NEW 里的数值并且返回之或者我们也可以构建一个完全新的返回记录/行．

例外

Postgres 不具有一个很好的例外处理模块．当分析器，规划器（调度器）/优化器或者执行器认为一个语句不能在处理下去了，整个事务都退出并且系统跳回主循环等待从客户应用过来的下一个查询．

我们可以'钩'在错误机制上来提示这种情况的发生．但是目前我们没有能力告诉（用户）是什么导致了退出（输入/输出转换错误，浮点数错误，分析错误）．并且此时的数据库后端可能处在一种不连贯的状态，所以退回到上层执行器或执行更多的命令可能摧毁整个数据库．而且此时事务退出的信息可能已经发送给了客户端应用，所以继续操作没有任何意义．

因此，PL/pgSQL 在函数或触发器操作时遇到退出的唯一一项操作是在 DEBUG 级别运行时输出一些附加的日志信息，报告在哪个函数和在那里（行号和语句类型）出了错．

例子

这里是几个例子用以说明书写 PL/pgSQL 函数是多么地容易．对于更复杂的例子，程序员们可以看看用于 PL/pgSQL 回归测试的程序．

书写 PL/pgSQL 的一个很糟糕的东西是单引号的使用．CREATE FUNCTION 的函数原文本必须是一个语言字符串．在一个语言字符串里的单引号要么是双引号或者是用反斜杠引用．我们仍然在寻找一种优美的代替物．同时，我们应该象下面例子那样使用双引号．将来任何版本的 Postgres 对此的任何解决方法都将保持向下兼容．

一些简单的 PL/pgSQL 函数

下面的两个 PL/pgSQL 函数与 C 语言里讨论的对应函数是一样的．

    CREATE FUNCTION add_one (int4) RETURNS int4 AS '

        BEGIN

            RETURN $1 + 1;

        END;

    ' LANGUAGE 'plpgsql';

   

    CREATE FUNCTION concat_text (text, text) RETURNS text AS '

        BEGIN

            RETURN $1 || $2;

        END;

    ' LANGUAGE 'plpgsql';

   

复合类型的 PL/pgSQL 函数

同样下面的是等效于 C 函数样例的 PL/pgSQL 函数．

    CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '

        DECLARE

            emprec ALIAS FOR $1;

            sallim ALIAS FOR $2;

        BEGIN

            IF emprec.salary ISNULL THEN

                RETURN ''f'';

            END IF;

            RETURN emprec.salary > sallim;

        END;

    ' LANGUAGE 'plpgsql';

   

PL/pgSQL 触发器过程

下面的触发器的作用是：任何时候表中插入或更新了行，当前的用户名和时间都记录入行中．并且它保证给出了雇员名称并且薪水是一个正数．

    CREATE TABLE emp (

        empname text,

        salary int4,

        last_date datetime,

        last_user name);

 

    CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS

        BEGIN

            -- Check that empname and salary are given

            IF NEW.empname ISNULL THEN

                RAISE EXCEPTION ''empname cannot be NULL value'';

            END IF;

            IF NEW.salary ISNULL THEN

                RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;

            END IF;

 

            -- Who works for us when she must pay for?

            IF NEW.salary < 0 THEN

                RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;

            END IF;

 

            -- Remember who changed the payroll when

            NEW.last_date := ''now'';

            NEW.last_user := getpgusername();

            RETURN NEW;

        END;

    ' LANGUAGE 'plpgsql';

 

    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

<