PL/pgSQL - SQL过程语言

  

目 录

 

1.       概述

1.1         使用PL/pgSQL的优点

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

2.       开发 PL/pgSQL 的一些提示

3.       PL/pgSQL结构

4.       声明

4.1         函数参数的别名

4.2         拷贝类型

4.3         行类型

4.4         记录类型

4.5         RENAME

5.       表达式

6.       基本语句

6.1         赋值

6.2         SELECT INTO

6.3         执行一个没有结果的表达式或者命令

6.4         执行动态命令

6.5         获取结果状态

7.       控制结构

7.1         从函数返回

7.2         条件

7.3         简单循环

7.4         遍历命令结构

7.5         捕获错误

8.       游标

8.1         声明游标变量

8.2         打开游标

8.3         使用游标

9.       错误和消息

10.    触发器过程

 

 

 

 

 

 

 

 

 

 

 

 

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

·         可用于创建函数和触发器过程；

·         为 SQL 语言增加控制结构；

·         可以执行复杂的计算；

·         继承所有用户定义类型，函数和操作符；

·         可以定义为被服务器信任（的语言）；

·         容易使用。

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

1       概述

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

在每个函数中用到的表达式和 SQL 命令在函数里首次使用的时候，PL/pgSQL 解释器创建一个准备好的执行规划（使用 SPI 管理器的SPI_prepare和SPI_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/pgSQL 的 EXECUTE语句动态地构造命令 — 代价是每次执行的时候都构造一个新的命令计划。

注意: PL/pgSQL 的EXECUTE语句和 PostgreSQL 服务器支持的EXECUTE语句（执行一个准备好的查询）没有关系。 服务器的EXECUTE语句不能在 PL/pgSQL 函数中使用（而且也没必要）。

服务器中的EXECUTE语句：EXECUTE plan_name [ (parameter [, ...] ) ]

EXECUTE 用户执行一个前面准备好的语句。因为一个准备好的查询只在会话的生命期里存在，那么准备好的查询必须是在当前会话的前些时候用 PREPARE 语句执行的。

如果创建语句的PREPARE 语句声明了一些参数， 那么传递给 EXECUTE 语句的必须是一个兼容的参数集， 否则就会生成一个错误。请注意（和函数不同），准备好的语句不会基于参数的类型或者个数重载：在一次数据库会话过程中，准备好的语句的名字必须是唯一的。

PREPARE -- 创建一个准备好的查询，语法如下：

PREPARE plan_name [ (datatype [, ...] ) ] AS statement

在使用完PREPARE创建的查询之后，可以使用DEALLOCATE（删除一个准备好的查询）,语法如：DEALLOCATE [ PREPARE ] plan_name。

1.1使用PL/pgSQL的优点

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

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

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

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

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

它们还可以接受或者返回任意用名字声明的复合类型（行类型）。还可以声明一个 PL/pgSQL 函数为返回record的函数， 意思是结果是一个行类型，这个行的字段是在调用它的查询中指定。

PL/pgSQL 函数还可以声明为接受并返回多态的类型anyelement和anyarray。一个多态的函数实际操作的数据类型可以在不同的调用环境中变化。关于多态类型，详细见下“多态类型”。

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

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

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

多态类型

两种特别有趣的伪类型是 anyelement 和 anyarray， 它们在一起称作多态类型。任何用这些类型定义 的函数就叫做多态函数。一种多态函数可以在许多不同的数据类型上操作，它们判断具体类型的方法是在一次调用中，使用实际传递进来的数据类型 来判断。

多态参数和结果是相互绑定，并且在分析查询调用的函数时解析成特定的数据类型。每个声明成 anyelement 的位置（参数或者返回类型）都允许拥有 一个特定的实际数据类型，但是在任何给定的调用过程中，它们都必须 是同样的类型。每个声明为 anyarray 的位置都可以是任何数组数据类型，但是，类似的，它们也不许都是同样的类型。如果有些 位置声明为 anyarray 而其它的位置声明为 anyelement， 那么在 anyarray 位置上的类型必须是元素类型与那些出现在 anyelement 位置上的同类型的数组。

因此，如果多于一个参数位置声明为一个多态类型，其实际效果是只允许某些实际参数类型的组合出现。比如，一个函数声明为 equal(anyelement, anyelement) 将接受任何两个输入值，只要它们的数据类型相同。

如果一个函数的的返回值声明为多态类型，那么至少有一个参数位置也是多态的，并且提供给参数的类型决定该词调用实际返回的类型。比如，如果没有数组下标 机制，那么我们可以定义一个函数实现下标的函数，像 subscript(anyarray, integer) returns anyelement。 这个声明约束实际上的第一个参数是一个数组类型，并且允许分析器从第一个参数的实际类型里推导出正确的返回类型。

2       开发 PL/pgSQL 的一些提示

用PL/pgSQL 做开发的一个好方法是简单地使用你喜欢的文本编辑器创建你的函数，然后在另外一个控制台里，用 psql 装载这些函数。如果你用这种方法， 那么用 CREATE OR REPLACE FUNCTION 写函数是个好主意。这样，你就可以重载文件以更新函数定义。比如：

CREATE OR REPLACE FUNCTION testfunc(integer)

RETURNS integer AS

$$

DECLARE

   -- 变量声明部分

BEGIN

....

END;

$$

LANGUAGE plpgsql;

在运行psql的时候，你可以用下面命令装载或者重载这样的函数定义文件：/i filename.sql；然后马上发出 SQL 命令测试该函数。

另外一个开发PL/pgSQL程序的好方法是用一种GUI的数据库访问工具，并且是实现了过程语言开发设施的那种。 这种工具中的一种就是 pgaccess，当然还有其他的。这些工具通常提供了一些很有用的功能，比如逃逸单引号，令重建和调试函数更简单等。

注：PL/pgSQL 函数的代码都是在 CREATE FUNCTION 里以一个字串文本的方式声明的。 如果你用两边包围单引号的常规方式写字串文本，那么任何函数体内的单引号都必须写双份；类似的是反斜杠也必须双份。双份引号非常乏味，在更复杂的场合下，代码可能会让人难以理解， 因为你很容易发现自己需要半打甚至更多相连的引号。 我们建议你用"美元符包围"的字串文本来写函数体。

引号处理：

·         单引号(‘) 函数中，如果字符串中有单引号出现，则使用2个单引号表示单引号；

·         双引号(‘”) 如果是双引号，则用4个单引号表示。

3       PL/pgSQL结构

PL/pgSQL是一种块结构的语言。函数定义的所有文本都必须是一个块。 一个块用下面的方法定义：

[ <<label>> ]

[ DECLARE

    declarations]

BEGIN

    Statements

END;

块中的每个声明和每条语句都是用一个分号终止的，如果一个子块在另外一个块里，那么 END 后面必须有个分号，如上所述；不过结束函数体的最后的 END 可以不要这个分号。

所有关键字和标识符都可以用混和大小写的方式来写。标识符被隐含地转换成小写字符，除非被双引号包围。

在 PL/pgSQL 里有两种类型地注释。一个双破折号（--） 引出一个扩展到该行结尾的注释。一个 /* 引出一个块注释，一直扩展到下一次 */ 的出现。 块注释不能嵌套，但是双破折号注释可以包围在块注释里面，并且双破折号可以隐藏一个块注释分隔符 /* 和 */。

在一个块的语句段里的任何语句都可以是一个子块。子块可以用于逻辑分组或者把变量局部化为作用于一个比较小的语句组。

在语句块前面的声明段（declarations section）里定义的变量在每次进入语句块时都初始化为它们的缺省值， 而不是每次函数调用时初始化一次。比如：

CREATE FUNCTION somefunc()

RETURNS integer AS

$$

DECLARE

   quantity integer := 30;

BEGIN

   RAISE NOTICE 'Quantity here is %', quantity; -- 在这里的数量是 30

   quantity := 50;

   --

   -- 创建一个子块

   --

   DECLARE

      quantity integer := 80;

   BEGIN

      RAISE NOTICE 'Quantity here is %', quantity; -- 在这里的数量是 80

   END;

   RAISE NOTICE 'Quantity here is %', quantity; -- 在这里的数量是 50

   RETURN quantity;

END;

$$

LANGUAGE plpgsql;

我们一定不要把PL/pgSQL里用于语句分组的 BEGIN/END 和用于事务控制的数据库命令搞混了。 PL/pgSQL的 BEGIN/END 只是用于分组（译注∶象 C 里的 {}）； 它们不会开始和结束一个事务。 函数和触发器过程总是在一个由外层命令建立起来的事务里执行 — 它们无法开始或者提交事务，因为 PostgreSQL 没有嵌套事务。 不过，一个包含 EXCEPTION 子句的块实际上形成一个子事务，它可以在不影响外层事务的情况下回滚。

4       声明

所有在块里使用的变量都必须在一个块的声明段里声明。（唯一的例外是一个FOR循环里的循环变量是在一个整数范围内迭代的，被自动声明为整数变量。）

PL/pgSQL变量可以用任意的 SQL 数据类型，比如integer，varchar和char。

下面是一些变量声明的例子：

user_id integer;

quantity numeric(5);

url varchar;

myrow tablename%ROWTYPE;

myfield tablename.fieldname%TYPE;

arow RECORD;

一个变量声明的一般性语法是：

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression];

如果给出了DEFAULT子句，那么它声明了在进入该块的时候赋予该变量的初始值。 如果没有给出DEFAULT子句，那么该变量初始化为 SQL 空值。 CONSTANT选项避免了该变量被赋值，这样其数值在该块的范围内保持常量。如果声明了NOT NULL，那么赋予NULL数值将导致一个运行时错误。 所以所有声明为NOT NULL的变量还必须声明一个非空的缺省值。

缺省值是在每次进入该块的时候计算的。因此，如果把 now() 赋予一个类型为 timestamp 的变量会令变量拥有函数实际调用的时间，而不是函数预编译的时间。

例子∶

quantity integer DEFAULT 32;

url varchar := 'http://mysite.com';

user_id CONSTANT integer := 10;

4.1函数参数的别名

传递给函数的参数都是用 $1，$2，等等这样的标识符。 为了增加可读性，我们可以为 $n 参数名声明别名。 然后别名或者数字标识符都可以指向参数值。

有两种创建别名的方法，比较好的是在 CREATE FUNCTION 命令里给出参数名， 比如：

CREATE FUNCTION sales_tax(subtotal real)

RETURNS real AS

$$

BEGIN

    RETURN subtotal * 0.06;

END;

$ 

LANGUAGE plpgsql;

另外一个方法，是PostgreSQL 8.0以前的唯一的方法，是明确地声明为别名，使用声明语法: name ALIAS FOR $n;

这个风格的同一个例子看起来像下面这样:

CREATE FUNCTION sales_tax(REAL)

RETURNS real AS 

$

DECLARE

    subtotal ALIAS FOR $1;

BEGIN

    RETURN subtotal * 0.06;

END;

$ 

LANGUAGE plpgsql;

注：如果一个PL/pgSQL函数的返回类型声明为一个多态类型(anyelement或anyarray)，那么就会创建一个特殊的参数：$0。我们就可以对这个参数进行操作。

例子：

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)

RETURNS anyelement AS

$$

DECLARE

    result ALIAS FOR $0;

BEGIN

    result := v1 + v2 + v3;

    RETURN result;

END;

$$

LANGUAGE plpgsql;

4.2拷贝类型

variable%TYPE

%TYPE 提供一个变量或者表字段的数据类型。 你可以用这个声明将要保存数据库数值的变量。比如，假如你在 users 表里面有一个字段叫 user_id。要声明一个和 users.user_id 类型相同的变量，你可以写：user_id users.user_id%TYPE;

通过使用 %TYPE，你必须知道你引用的结构的数据类型， 并且，最重要的是，如果被引用项的数据类型在将来变化了（比如：你把 user_id 的类型从 integer 改成 real），你也不需要修改你的函数定义。

%TYPE 对多态的函数特别有用，因为内部变量的数据类型可能在不同调用中是不一样的。我们可以通过给函数的参数或者结果占位符附加 %TYPE 的方法来创建合适的变量。

4.3行类型

name table_name%ROWTYPE;

name composite_type_name;

一个复合类型变量叫做行变量（或者row-typeSELECT或者 FOR命令结果的完整一行，只要命令的字段集匹配该变量声明的类型。行数值的独立的字段是使用常用的点表示法访问的，比如 rowvar.field。变量）。 这样的一个变量可以保存一次

一个行变量可以声明为和一个现有的表或者视图的行类型相同，方法是使用 table_name%ROWTYPE 表示法； 或者你也可以声明它的类型是一个复合类型的名字。（因为每个表都有一个相关联的同名数据类型，在 PostgreSQL 里实在是无所谓你写不写 %ROWTYPE。但是有 %ROWTYPE 的形式移植性更好。）

函数的参数可以是复合类型（表的完整行）。这个时候，对应的标识符 $n 将是一个行变量，并且可以从中选取字段，比如 $1.user_id。

在一个行类型的变量中，只可以访问用户定义的表中行的属性，不包括 OID 或者其他系统属性（因为该行可能来自一个视图）。 该行类型的数据域继承表中象 char(n) 这种类型字段的尺寸和精度。

这里是一个使用复合类型的例子：

CREATE FUNCTION merge_fields(t_row tablename)

RETURNS text AS

$$

DECLARE

    t2_row table2name%ROWTYPE;

BEGIN

   SELECT * INTO t2_row FROM table2name WHERE ... ;

    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;

END;

$$

LANGUAGE plpgsql;

 

SELECT merge_fields(t.*) FROM tablename t WHERE ... ;

4.4记录类型

name RECORD;

纪录变量类似行类型变量，但是它们没有预定义的结构。它们在SELECT或者FOR命令中获取实际的行结构。 一个行变量的子结构可以在每次赋值的时候改变。这样做的一个结果是：在一个记录变量被赋予数值之前，它没有子结构， 并且任何对其中的数据域进行访问的企图都将产生一个运行时错误。

请注意 RECORD 不是真正的数据类型，只是一个占位符。 我们还应该意识到在把一个 PL/pgSQL 函数声明为返回record类型的时候， 它和一个记录变量的概念并不完全相同，即使这个函数可能使用一个记录变量保存它的结果也如此。 在两种情况下，在书写函数的时候，实际的行结构都是不知道的，但是对于返回 record 的函数来说， 实际的结构是在调用它的查询被分析的时候决定的，而行变量可以在运行中改变其行结构。

4.5RENAME

RENAME oldname TO newname;

你可以用 RENAME 声明修改一个变量，记录或者行的名字。 如果 NEW 或者 OLD 在个触发器过程里被另外一个名字引用， 那么这个东西就很有用。又见 ALIAS。

例子：

RENAME id TO user_id;

RENAME this_var TO that_var;

注意: RENAME 在PostgreSQL7.3 里好像有问题。修补这个毛病的优先级比较低， 因为 ALIAS 覆盖了大多数 RENAME 的实际用途。

5       表达式

所有在PL/pgSQL 语句里使用的表达式都是用服务器的普通SQL执行器进行处理的。 实际上，类似下面的查询：SELECT expression。是使用 SPI 管理器执行的。 在计算之前，出现PL/pgSQL变量标识符的地方先被参数代替， 然后变量的实际值放在参数数组里传递给执行器。 这样就允许SELECT的执行计划只需要准备一次，并且在随后的计算中复用。

PostgreSQL 的主分析器做的类型检查对常量数值的代换有一些副作用。 详细说来就是下面这两个函数做的事情有些区别：

CREATE FUNCTION logfunc1 (logtxt text) RETURNS timestamp AS $

BEGIN

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

        RETURN 'now';

END;

$ LANGUAGE plpgsql;

和 

CREATE FUNCTION logfunc2 (logtxt text) RETURNS timestamp AS $

DECLARE

        curtime timestamp;

    BEGIN

        curtime := 'now';

        INSERT INTO logtable VALUES (logtxt, curtime);

        RETURN curtime;

END;

$ LANGUAGE plpgsql;

在 logfunc1() 的实例里， PostgreSQL 的主分析器在为 INSERT 准备执行计划的时候知道字串 'now' 应该解释成 timestamp 类型，因为 logtable 的目标字段就是该类型。所以，它会在这个时候从这个字串中计算一个常量， 然后在该服务器的整个生存期中的所有 logfunc1 调用中使用这个常量。不消说，这可不是程序员想要的。 

在logfunc2里， PostgreSQL 的主分析器并不知道 now 应该转换成什么类型， 因此它返回一个包含字符串 now 的类型为 text 的数据值。 在随后给局部变量curtime赋值时， PL/pgSQL解释器通过调用 text_out和timestamp_in 把这个字符串转换成 timestamp 类型的变量。 因此，计算出的时戳就会按照程序员希望的那样在每次执行的时候都更新。 

记录变量的易变性天性在这种结合上提出了一个问题。 在一个记录变量在语句或者表达式中使用时， 该字段的数据类型在同一个表达式的不同调用期间不能修改， 因为该表达式准备使用的是运行第一次到达该表达式时出现的数据类型。 在写处理超过一个表的事件的触发器过程的时候一定要把这个记住。（必要时可以用EXECUTE绕开这个问题。） 

6       基本语句

本节以及随后的一节里，我们描述所有 PL/pgSQL 明确可以理解的语句类型。任何无法识别为这样类型的语句将被做为 SQL 命令看待，并且被发送到主数据库引擎执行（在将语句中用到的任何 PL/pgSQL 变量进行替换之后）。因此，举例来说，SQL INSERT，UPDATE，和 DELETE 命令可能被认为是 PL/pgSQL 语句，但是它们并未在此明确列出。

6.1赋值

给一个变量或行/记录赋值用下面方法：identIFier = expression;

如上所述，这样的语句中的表达式是用一个发送到主数据库引擎的 SQL SELECT 命令计算的。该表达式必须生成单一的数值。

如果表达式的结果数据类型和变量数据类型不一致，或者变量具有已知的尺寸/精度（象 char(20)）， 结果值将隐含地被PL/pgSQL解释器用结果类型的输出函数和变量类型的输入函数转换。要注意的是，如果结果数值的字串形式不是输入函数可以接受的形式， 那么这样做可能导致类型输入函数产生的运行时错误。

例子：

user_id = 20;

tax = subtotal * 0.06;

6.2SELECT INTO

生成多个列（但只有一行）的SELECT命令的结果可以赋予一个记录变量， 行类型变量，或者一个标量变量的列表。这是用下面方法实现的：

SELECT INTO target select_expressions FROM ...;

或

SELECT select_expressions INTO target FROM ...; -- 推荐这中方法

这里的 target 可以是一个记录变量， 行变量，或者一个用逗号分隔的简单变量和记录/行字段的列表。select_expressions 和命令的剩余部分和普通 SQL 一样。

请注意这个构造和 PostgreSQL 普通的SELECT INTO构造的解释是不一样的， 后者的INTO目标是一个新创建的表。 （如果你想在 PL/pgSQL 函数里从一个SELECT 的结果中创建一个表，那么使用 CREATE TABLE ... AS SELECT 语法。）

如果将一行或者一个变量列表用做目标，那么选出的数值必需精确匹配目标的结构，否则就会产生运行时错误。如果目标是一个记录变量，那么它自动将自己配置成命令结果列的行类型。

除了INTO子句，剩下的SELECT语句和普通的 SQL SELECT命令完全一样， 并且你可以使用SELECT的全部能力。

INTO 子句几乎可以出现在 SELECT 语句的任何地方。 习惯上它是跟在 SELECT 后面，就像上面写的那样， 或者就在 FROM 之前 — 也就是说，在 select_expressions 列表之前或者之后。

如果命令返回零行，则给目标赋与空值。 如果命令返回多行，那么将第一行赋与目标并抛弃其它的行。（请注意：除非你用了ORDER BY，否则"第一行"是不明确的。）

INTO子句可以出现在SELECT命令里的几乎任何地方。

在一个 SELECT INTO 语句之后，你可以检查特殊变量 FOUND来判断一个赋值是否成功， 也就是说，查询至少返回一行。例如：

SELECT INTO myrec * FROM emp WHERE empname = myname;

IF NOT FOUND THEN

    RAISE EXCEPTION 'employee % not found', myname;

END IF;

要测试一个记录/行结果是否为空，你可以使用 IS NULL 条件。不过，这个时候没有任何办法来判断是否有额外的行被抛弃。下面是一个例子，处理没有返回行的情况：

DECLARE

    users_rec RECORD;

BEGIN

    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN

        -- 用户没有输入主页，返回http://

        RETURN 'http://';

    END IF;

END;

6.3执行一个没有结果的表达式或者命令

有时候我们希望计算一个表达式或者一个命令，但是却丢弃其结果（通常因为我们经常调用一些存在有用的副作用但是不存在有用结果值的函数）。要在 PL/pgSQL 里干这件事， 你可以使用PERFORM语句：PERFORM query;

这条语句执行一个 query并且丢弃结果。query 的写法和你平常写SQL SELECT命令是一样的，只是把开头的关键字SELECT替换成PERFORM。PL/pgSQL 的变量和平常一样代换到命令中。同样，如果命令生成至少一行，那么特殊的变量 FOUND 设置为真，如果没有生成行，则为假。

注意: 我们可能希望没有INTO子句的SELECT也能满足这样的需要，但是目前可以接受的唯一的方法是PERFORM。

一个例子： PERFORM create_mv('cs_session_page_requests_mv', my_query);

6.4执行动态命令

你经常会希望在你的PL/pgSQL函数里生成动态命令。 也就是那些每次执行的时候都会涉及不同表或不同数据类型的命令。在这样的情况下，PL/pgSQL 试图为命令缓冲执行计划的一般企图将不再合适。 为了处理这样的问题，我们提供了EXECUTE语句：

EXECUTE command-string;

这里的 command-string 是一个生成字串（类型为 text）的表达式，该字串包含要执行的命令。 该字串的文本将被传递给 SQL 引擎。

请特别注意在该命令字串里将不会发生任何 PL/pgSQL 变量代换。变量的数值必需在构造命令字串的时候插入该字串。

和所有其它在PL/pgSQL里的命令不同， 一个由EXECUTE语句运行的命令在服务器生命期内并不只准备和保存一次。 相反，在该语句每次运行的时候，命令都准备一次。 命令字串可以在过程里动态地生成以便于对各种不同的表和字段进行操作。

来自SELECT命令的结果被EXECUTE抛弃，并且目前EXECUTE 里面还不支持SELECT INTO。所以我们没有办法从一个动态创建的 SELECT 中， 使用简单的 EXECUTE 命令抽取结果。 但是有其它两种方法可以实现里抽取结果：一种是是使用FOR-IN-EXECUTE方式，另外一种是和 OPEN-FOR-EXECUTE 一起用游标。

要插入到构造出来的查询中的动态数值也需要特殊的处理，因为他们自己可能包含引号字符。 一个例子（除了特别说明之外，这里我们都假设你使用了美元符包围）：

EXECUTE 'UPDATE tbl SET '

        || quote_ident(columnname)

        || ' = '

        || quote_literal(newvalue)

        || ' WHERE ...';

这个例子显示了函数 quote_ident(text) 和 quote_literal(text) 的使用。 为了安全，包含字段和表标识符的变量应该传递给函数 quote_ident。 那些包含数值的变量，如果其值在构造出来态命令字串里应外是文本字串，那么应该传递给 quote_literal。 它们俩都会采取合适的步骤把输入文本包围在单或双引号里并且对任何嵌入其中的特殊字符进行合适的逃逸处理。

请注意美元符包围只对包围固定文本有用。如果想象下面这样做上面的例子，那就太糟糕了

EXECUTE 'UPDATE tbl SET '

        || quote_ident(colname)

        || ' = $$'

        || newvalue

        || '$$ WHERE ...';

因为如果 newvalue 的内容碰巧有$$，那么这段代码就有毛病了。 同样的问题可能出现在你选用的任何美元符包围分隔符上。 因此，要想安全地包围事先不知道地文本，你必须使用 quote_literal。

6.5获取结果状态

有好几种方法可以判断一条命令的效果。第一个方法是使用 GET DIAGNOSTICS，它的形式如下：

GET DIAGNOSTICS variable = item [ , ... ] ;

这条命令允许我们检索系统状态标识符。每个 item 是一个关键字，表示一个将要赋予该特定变量的状态值（该变量应该和要接收的数值类型相同）。当前可用的状态项有 ROW_COUNT， 最后一个发送给 SQL 引擎的 SQL 命令处理的行的数量，和 RESULT_OID，最后一条 SQL 命令插入的最后一行的 OID。请注意 RESULT_OID 只有在一个INSERT命令之后才有用。

一个例子：GET DIAGNOSTICS var_integer = ROW_COUNT;

另外一个判断命令效果的方法是一个类型为 boolean 的特殊变量 FOUND。 FOUND在每个 PL/pgSQL 函数里开始都为假。它被下列语句设置：

·         一个SELECT INTO语句如果返回一行则设置 FOUND 为真，如果没有返回行则设置为假;

·         一个PERFORM语句如果生成（或抛弃）一行，则设置 FOUND 为真，如果没有生成行则为假;

·         如果至少影响了一行，那么UPDATE，INSERT，和DELETE语句设置 FOUND 为真，如果没有行受影响则为假

·         FETCH语句如果返回行则设置 FOUND 为真， 如果不返回行则为假；

·         一个FOR语句如果迭代了一次或多次，则设置 FOUND 为真，否则为假。这个规律适用于所有FOR语句的三种变体 （整数FOR循环，记录集的FOR循环，以及动态记录集FOR循环）。 只有在FOR循环退出的时候才设置 FOUND； 在循环执行的内部，FOUND 不被FOR语句修改， 但是在循环体里它可能被其他语句的执行而修改。

FOUND 是每个 PL/pgSQL 里的局部变量；它的任何修改只影响当前的函数。

7       控制结构

控制结构可能是 PL/pgSQL 中最有用的（以及最重要）的部分了。利用 PL/pgSQL 的控制结构， 你可以以非常灵活而且强大的方法操纵 PostgreSQL 的数据。

7.1从函数返回

有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT。

·         RETURN

RETURN expression;

带表达式的 RETURN 是用于终止函数, 然后 expression 的值返回给调用者。

如果返回标量类型，那么可以使用任何表达式。表达式的类型将被自动转换成函数的返回类型， 就像我们在赋值中描述的那样。 要返回一个复合（行）数值，你必须写一个记录或者行变量做 expression。

一个函数的返回值不能是未定义。如果控制到达了函数的最顶层的块而没有碰到一个 RETURN 语句， 那么它就会发生一个错误。

请注意如果你声明了该函数返回 void，那么仍然必须声明 RETURN 语句；但是，跟在 RETURN 后面的表达式是可选的，并且在任何情况下都会被忽略。

·         RETURN NEXT

RETURN NEXT expression;

如果一个 PL/pgSQL 函数声明为返回 SETOF sometype， 那么遵循的过程则略有不同。在这种情况下，要返回的独立的项是在 RETURN NEXT 命令里声明的，然后最后有一个不带参数的 RETURN 命令用于告诉我们这个函数已经完成执行了。 RETURN NEXT 可以用于标量和复合数据类型；对于后者，将返回一个完整的结果"表"。

使用 RETURN NEXT 的函数应该按照下面的风格调用：SELECT * FROM some_func();

也就是说，这个函数是用做FROM子句里面的一个表数据源的。

RETURN NEXT 实际上并不从函数中返回； 它只是简单地把表达式的值保存起来。 然后执行继续执行 PL/pgSQL 函数里的下一条语句。 随着后继的 RETURN NEXT 命令的执行， 结果集就建立起来了。最后的一个不需要参数的 RETURN， 导致控制退出该函数。

注意: 目前的 PL/pgSQL 的 RETURN NEXT 实现在从函数返回之前把整个结果集都保存起来，就象上面描述的那样。 这意味着如果一个 PL/pgSQL 函数生成一个非常大的结果集， 性能可能会很差：数据将被写到磁盘上以避免内存耗尽， 但是函数在完成整个结果集的生成之前不会退出。将来的 PL/pgSQL 版本可能会允许用户定义没有这样限制的返回集合的函数。 目前，数据开始向磁盘里写的时刻是由配置变量 work_mem 控制的。 拥有足够内存的管理员如果想在内存里存储更大的结果集， 则可以考虑把这个参数增大一些。

7.2条件

IF 语句让你可以根据某种条件执行命令。 PL/pgSQL有五种形式的IF：

·   IF ... THEN

·   IF ... THEN ... ELSE

·   IF ... THEN ... ELSE IF

·   IF ... THEN ... ELSIF ... THEN ... ELSE

·   IF ... THEN ... ELSEIF ... THEN ... ELSE

1）        IF-THEN

IF boolean-expression THEN

Statements

END IF;

IF-THEN语句是IF的最简单形式。如果条件为真， 在THEN和END IF之间的语句将被执行。 否则，将忽略它们。

例子：

IF v_user_id <> 0 THEN

    UPDATE users SET email = v_email WHERE user_id = v_user_id;

END IF;

2）        IF ... THEN ... ELSE

IF boolean-expression THEN

    Statements

ELSE

    Statements

END IF;

IF-THEN-ELSE语句增加了IF-THEN的分支， 让你可以声明在条件计算结果为假的时候执行的语句。

例子：

IF parentid IS NULL OR parentid = '' THEN 

    RETURN fullname;

ELSE

    RETURN hp_true_filename(parentid) || '/' || fullname;

END IF;

3）        IF ... THEN ... ELSE IF

IF语句可以嵌套并且在下面的例子中：

IF demo_row.sex = 'm' THEN

 pretty_sex := 'man';

ELSE

 IF demo_row.sex = 'f' THEN

    pretty_sex := 'woman';

 END IF;

END IF;

如果你使用这种形式，那么你实际上就是在另外一个IF语句的ELSE 部分嵌套了一个IF语句．因此你需要一个END IF语句 给每个嵌套的IF，另外还要一个给父IF-ELSE用． 这么干是可以的，但是如果我们有太多候选项需要检查，那么就会变得很乏味．因此有下面的形式。

4）        IF ... THEN ... ELSIF ... THEN ... ELSE

IF boolean-expression THEN

    Statements

[ ELSIF boolean-expression THEN

    statements

[ ELSIF boolean-expression THEN

    statements

    ...]]

[ ELSE

    statements]

END IF;

IF-THEN-ELSIF-ELSE提供了一种更方便的方法用于在一条语句中检查许多候选条件。 形式上它和嵌套的IF-THEN-ELSE-IF-THEN命令相同， 但是只需要一个END IF。

这里是一个例子：

IF number = 0 THEN

    result := 'zero';

ELSIF number > 0 THEN

    result := 'positive';

ELSIF number < 0 THEN

    result := 'negative';

ELSE

    -- 另外一个唯一的可能是它是空值

    result := 'NULL';

END IF;

5）        IF ... THEN ... ELSEIF ... THEN ... ELSE

ELSEIF 是 ELSIF 的别名。

7.3简单循环

使用LOOP，WHILE，FOR 和 EXIT 语句，你可以控制你的 PL/pgSQL 函数重复一系列命令。

（1）    LOOP

[<<label>>]

LOOP

    Statements

END LOOP;

LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。 可选的标签可以由EXIT语句使用，用于在嵌套循环中声明应该结束哪一层循环。

（2）    EXIT

EXIT [label] [ WHEN expression];

如果没有给出 label， 那么退出最内层的循环，然后执行跟在END LOOP后面的语句。 如果给出 label， 那么它必须是当前或者更高层的嵌套循环块或者块的标签。 然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。

如果出现了WHEN，循环退出只发生在声明的条件为真的时候， 否则控制会落到EXIT后面的语句上。

EXIT 可以用于在所有的循环类型中提前退出； 它并不仅限于在无条件循环中使用。

例子：

LOOP

    -- 一些计算

    IF count > 0 THEN

        EXIT; -- exit loop

    END IF;

END LOOP;

 

BEGIN

    -- 一些计算

    IF stocks > 100000 THEN

        EXIT; -- 导致从 BEGIN 块里退出

    END IF;

END;

（3）    WHILE

[<<label>>]

WHILE expression LOOP

    Statements

END LOOP;

只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.

比如:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP

    -- 可以在这里做些计算

END LOOP;

（4）    FOR

[<<label>>]

FOR name IN [ REVERSE ] expression .. expression LOOP

    Statements

END LOOP;

这种形式的FOR对一定范围的整数数值进行迭代的循环。 变量name 会自动定义为integer类型并且只在循环里存在。 给出范围上下界的两个表达式在进入循环的时候计算一次。 迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP

 -- 这里可以放一些表达式

    RAISE NOTICE 'i IS %', i;

END LOOP;

FOR i IN REVERSE 10..1 LOOP

    -- 这里可以放一些表达式

END LOOP;

如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。 而且不会抛出任何错误。

7.4遍历命令结构

使用不同类型的FOR循环，你可以遍历一个命令的结果并且相应的操作哪些数据。语法是：

[<<label>>]

FOR record_or_row IN query LOOP

    Statements

END LOOP;

这里的记录或者行变量将相继被赋予所有来自query（必须是一条 SELECT 命令）的行， 并且循环体将为每行执行一次。下面是一个例子：

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$

DECLARE

     mviews RECORD;

BEGIN

     PERFORM cs_log('Refreshing materialized views...');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key

LOOP

    -- 现在 "mviews" 里有了一条来自 cs_materialized_views 的记录

PERFORM cs_log('Refreshing materialized view ' 

|| quote_ident(mviews.mv_name) || ' ...');

EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);

EXECUTE 'INSERT INTO ' || quote_ident(mview.mv_name) || ' ' || mview.mv_query;

END LOOP;

PERFORM cs_log('Done refreshing materialized views.');

RETURN 1;

END;

$ LANGUAGE plpgsql;

如果循环是用一个EXIT语句终止的，那么在循环之后你仍然可以访问最后赋值的行。

FOR-IN-EXECUTE语句是遍历所有行的另外一种方法：

[<<label>>]

FOR record_or_row IN EXECUTE text_expression LOOP

    Statements

END LOOP;

这个例子类似前面的形式，只不过源SELECT语句声明为了一个字串表达式， 这样它在每次进入FOR循环的时候都会重新计算和生成执行计划。 这样就允许程序员在一个预先规划好了的命令所获得的速度，和一个动态命令所获得的灵活性（就象一个简单的EXECUTE语句那样）之间进行选择。

注意: PL/pgSQL 分析器目前区分两种类型的FOR循环（整数或者返回记录的）： 方法是检查是否有任何 .. 出现在 IN 和 LOOP 之间的圆括弧之外。 如果没有看到 ..，那么这个循环就是在数据行上的循环。 如果误敲了 .. 就很可能会导致像下面这样的错误信息： "loop variable of loop over rows must be a record or row variable"， 而不是我们以为会看到的简单的语法错误。

7.5捕获错误

缺省时，一个在 PL/pgSQL 函数里发生的错误退出函数的执行，并且实际上是其周围的事务也会退出。你可以使用一个带有 EXCEPTION 子句的 BEGIN 块捕获错误并且从中恢复。其语法是正常的 BEGIN 块语法的一个扩展：

[ <<label>> ]

[ DECLARE

    declarations]

BEGIN

    Statements

EXCEPTION

    WHEN condition [ OR condition ... ] THEN

        handler_statements

    [ WHEN condition [ OR condition ... ] THEN

          handler_statements

      ... ]

END;

如果没有发生错误，这种形式的块只是简单地执行所有 statements， 但是如果在 statements 里发生了一个错误， 则对 statements 的进一步处理将废弃，控制传递到了 EXCEPTION 列表。 系统搜索这个列表，寻找匹配发生的错误的第一个元素。如果找到匹配，则执行对应的 handler_statements，然后控制传递到 END 之后的下一个语句。 如果没有找到匹配，该错误就会广播出去，就好像根本没有 EXCEPTION 子句一样： 该错误可以被一个包围块用 EXCEPTION 捕获，如果没有包围块，则退出函数的处理。

condition 名字可以是【附录A】里显示的任何名字。 一个范畴名匹配任意该范畴里的错误。特殊的条件名 OTHERS 匹配除了 QUERY_CANCELED 之外的所有错误类型。（我们可以用名字捕获 QUERY_CANCELED，不过通常是不明智的。）条件名是大小写无关的。

如果在选中的 handler_statements 里发生了新错误， 那么它不能被这个 EXCEPTION 子句捕获，而是传播出去。 一个外层的 EXCEPTION 子句可以捕获它。

如果一个错误被 EXCEPTION 捕获，PL/pgSQL 函数的局部变量保持错误发生的时候的原值，但是所有该块中想固化在数据库中的状态都回滚。作为一个例子，让我们看看下面片断：

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');

BEGIN

        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';

        x := x + 1;

        y := x / 0;

    EXCEPTION

        WHEN division_by_zero THEN

            RAISE NOTICE 'caught division_by_zero';

RETURN x;

END;

当控制到达给 y 赋值的地方的时候，它会带着一个 division_by_zero 错误失败。 这个错误将被 EXCEPTION 子句波获。而在 RETURN 语句里返回的数值将是 x 的增量值。 但是，在该块之前的 INSERT 将不会回滚，因此最终的结果是数据库包含 Tom Jones 而 不是 Joe Jones。

提示: 进入和退出一个包含 EXCEPTION 子句的块要比不包含的块开销大的多。 因此，不必要的时候不要使用 EXCEPTION.

8       游标

如果不想一次执行整个命令，我们可以设置一个封装该命令的 游标，然后每次读取几行命令结果。 这么干的一个原因是在结果包含数量非常大的行时避免内存耗尽。 （当然，PL/pgSQL 用户通常不必担心这个，因为 FOR 循环自动在内部使用一个游标以避免内存问题。） 一个更有趣的用法是某个函数可以返回一个它创建的游标的引用，这样就允许调用者读取各行。这就提供了一种从函数返回一个结果集的手段。

8.1声明游标变量

所有在 PL/pgSQL 里对游标的访问都是通过游标变量实现的，它总是特殊的数据类型 refcursor。 创建一个游标变量的一个方法是把它声明为一个类型为 refcursor 的变量。 另外一个方法是使用游标声明语法，通常是下面这样：

name CURSOR [ ( arguments ) ] FOR query ;

如果有 arguments，那么它是一个逗号分隔的name datatype配对的列表，它们定义那些将会用参数值替换掉的所给出命令中的名字。 实际用于代换这些名字的数值将在后面声明，在游标打开之后。

几个例子∶

DECLARE

    curs1 refcursor;

    curs2 CURSOR FOR SELECT * FROM tenk1;

    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

所有这三个变量都是类型为 refcursor， 但是第一个可以用于任何命令，而第二个已经绑定 了一个声明完整的命令，最后一个是绑定了一个带参数的命令。 （key 将在游标打开的时候被代换成一个整数。） 变量 curs1 可以称之为未绑定的， 因为它没有和任何查询相绑定。

8.2打开游标

在你使用游标检索行之前，你必需宪打开它。（这是和 SQL 命令 DECLARE CURSOR 相等的操作。） PL/pgSQL 有三种形式的OPEN语句， 两种用于未绑定的游标变量，另外一种用于绑定的游标变量。

·         OPEN FOR SELECT

OPEN unbound_cursor FOR SELECT ...;

该游标变量打开，并且执行给出的查询。游标不能是已经打开的，并且它必需是声明为一个未绑定的游标（也就事说，声明为一个简单的 refcursor 变量）。 SELECT 命令是和其它在 PL/pgSQL 里的 SELECT 命令平等对待的：先代换 PL/pgSQL 的变量名，而且执行计划为将来可能的复用缓存起来。

例子：OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

·         OPEN FOR EXECUTE

OPEN unbound_cursor FOR EXECUTE query-string;

打开游标变量并且执行给出的查询。游标不能是已打开的，并且必须声明为一个未绑定的游标（也就是说，是一个简单的 refcursor 变量）。命令是用和那些用于 EXECUTE 命令一样的方法声明的字串表达式， 这样，我们就有了命令可以在两次运行间发生变化的灵活性。

例子：OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

·         打开一个绑定的游标

OPEN bound_cursor [ ( argument_values ) ];

这种形式的OPEN用于打开一个游标变量，该游标变量的命令是在声明的时候和它绑定在一起的。 游标不能是已经打开的。 当且仅当该游标声明为接受参数的时候，语句中才必需出现一个实际参数值表达式的列表。这些值将代换到命令中。一个绑定的游标的命令计划总是认为可缓冲的 -- 这种情况下没有等效的EXECUTE。

例子：

OPEN curs2;

OPEN curs3(42);

8.3使用游标

一旦你已经打开了一个游标，那么你就可以用这里描述的语句操作它。

这些操作不需要发生在和打开该游标开始操作的同一个函数里。你可以从函数里返回一个 refcursor 数值，然后让调用者操作该游标。（在内部，refcursor值只是一个包含该游标命令的活跃查询的信使的字串名。这个名字可以传来传去，可以赋予其它refcursor 变量等等，也不用担心扰乱信使。）

所有信使在事务的结尾都会隐含地关闭。因此一个refcursor值只能在该事务结束前用于引用一个打开的游标。

1>      FETCH

FETCH cursor INTO target;

FETCH从游标中检索下一行到目标中，目标可以是一个行变量，一个记录变量，或者是一个逗号分隔的普通变量的列表，就象SELECT INTO里一样。 和SELECT INTO一样，你可以使用特殊变量FOUND检查是否检索出一个行。

例子：

FETCH curs1 INTO rowvar;

FETCH curs2 INTO foo, bar, baz;

2>      CLOSE

CLOSE cursor;

CLOSE关闭支撑在一个打开的游标下面的信使。这样我们就可以在事务结束之前施放资源，或者释放掉该游标变量，用于稍后再次打开。

例子： CLOSE curs1;

3>      返回游标

PL/pgSQL 函数可以向调用者返回游标。 这个功能用于从函数里返回多行或多列。要想这么做的时候， 该函数打开游标并且把该游标的名字返回给调用者。 调用者然后从游标里FETCH行。 游标可以由调用者关闭，或者是在事务结束的时候自动关闭。

函数返回的游标名可以由调用者声明或者自动生成。要声明一个信使的名字，只要再打开游标之前，给 refcursor 变量赋予一个字串就可以了。 refcursor 变量的字串值将被 OPEN 当作下层的信使的名字使用。 不过，如果 refcursor 变量是空，那么 OPEN 将自动生成一个和现有信使不冲突的名字， 然后将它赋予 refcursor 变量。

注意: 一个绑定的游标变量其名字初始化为对应的字串值，因此信使的名字和游标变量名同名，除非程序员再打开游标之前通过赋值覆盖了这个名字。但是一个未绑定的游标变量初始化的时候缺省是空， 因此它会收到一个自动生成的唯一的名字，除非被覆盖。

下面的例子显示了一个调用者声明游标名字的方法：

CREATE TABLE test (col text);

INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '

BEGIN

       OPEN $1 FOR SELECT col FROM test;

       RETURN $1;

END;

' LANGUAGE plpgsql;

 

BEGIN;

SELECT reffunc('funccursor');

FETCH ALL IN funccursor;

COMMIT;

 

下面的例子使用了自动生成的游标名：

CREATE FUNCTION reffunc2() RETURNS refcursor AS '

DECLARE

        ref refcursor;

BEGIN

        OPEN ref FOR SELECT col FROM test;

        RETURN ref;

END;

' LANGUAGE plpgsql;

 

BEGIN;

SELECT reffunc2();

      reffunc2

 <unnamed cursor 1>

(1 row)

 

FETCH ALL IN "<unnamed cursor 1>";

COMMIT;

 

下面的例子显示了从一个函数里返回多个游标的方法：

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$

BEGIN

    OPEN $1 FOR SELECT * FROM table_1;

    RETURN NEXT $1;

    OPEN $2 FOR SELECT * FROM table_2;

    RETURN NEXT $2;

    RETURN;

END;

$$ LANGUAGE plpgsql;

 

-- 需要在事务里使用游标。

BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;

FETCH ALL FROM b;

COMMIT;

9       错误和消息

利用RAISE语句报告信息以及抛出错误。

RAISE level 'format' [, variable [, ...]];

可能的级别有DEBUG(向服务器日志写信息)， LOG（向服务器日志写信息，优先级更高），INFO， NOTICE和WARNING （把信息写到服务器日志以及转发到客户端应用,优先级逐步升高）和 EXCEPTION抛出一个错误（通常退出当前事务）。 某个优先级别的信息是报告给客户端还是写到服务器日志， 还是两个都做是由 log_min_messages和 client_min_messages配置变量控制的。

在格式字串里，% 被下一个可选参数的外部表现形式代替。 要发出一个文本的 %，你要写 %%。 请注意可选的参数必须是简单的变量，不能是表达式，而且格式必须是一个简单的字串文本。

在这个例子里，v_job_id的值将代替字串中的%：

RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;

这个例子将会带着给出的错误信息退出事务：

RAISE EXCEPTION 'Inexistent ID --> %',user_id;

RAISE EXCEPTION 目前总是生成同样的 SQLSTATE 代码，P0001， 不管调用它的信息是什么。我们可以用 EXCEPTION ... WHEN RAISE_EXCEPTION THEN ... 捕获这样的例外，但是我们无法从一个 RAISE 里告诉另外一个相关的状态。

10            触发器过程

PL/pgSQL 可以用于定义触发器过程。 一个触发器过程是用 CREATE FUNCTION 命令创建的， 创建的形式是一个不接受参数并且返回 trigger 类型的函数。 请注意该函数即使在 CREATE TRIGGER 声明里声明为准备接受参数， 它也必需声明为无参数 — 触发器的参数是通过 TG_ARGV 传递的，下面有描述。

在一个 PL/pgSQL 函数当做触发器调用的时候，系统会在顶层的声明段里自动创建几个特殊变量。有如下这些：

·   NEW  数据类型是 RECORD； 该变量为INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。 在语句级别的触发器里，这个变量是 NULL;

·   OLD 数据类型是 RECORD； 该变量为 INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。 在语句级别的触发器里，这个变量是 NULL;

·   TG_NAME 数据类型是 name；该变量包含实际触发的触发器名。 Fired;

·   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 或者是 一个与导致触发器运行的表的记录/行完全一样的结构的数据。

因BEFORE触发的行级别的的触发器可以返回一个 NULL，告诉触发器管理器忽略对该行剩下的操作（也就是说，随后的触发器将不再执行，并且不会对该行产生INSERT/UPDATE/DELETE动作）。 如果返回了一个非 NULL 的行，那么将继续对该行数值进行处理。 请注意，返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行。 我们可能用一个值直接代替NEW里的某个数值并且返回之，或者我们也可以构建一个完全新的记录/行再返回。

BEFORE 或者 AFTER语句级别的触发器， 或者一个AFTER 行级别的触发器的返回值将总是被忽略； 它们也可以返回 NULL 来忽略返回值。不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。

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

CREATE TABLE emp (

    empname text,

    salary integer,

    last_date timestamp,

    last_user text

);

CREATE FUNCTION emp_stamp () RETURNS trigger AS $emp_stamp$

BEGIN

    -- 检查是否给出了 empname 和 salary

        IF NEW.empname ISNULL THEN

            RAISE EXCEPTION 'empname cannot be null';

        END IF;

        IF NEW.salary ISNULL THEN

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

        END IF;

        -- 我们必须付帐给谁？

        IF NEW.salary < 0 THEN

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

        END IF;

        -- 记住何时何人的薪水被修改了

        NEW.last_date := 'now';

        NEW.last_user := current_user;

        RETURN NEW;

END;

$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

另外一个向表里记录变化的方法涉及创建一个新表，然后为后来发生的每次插入、更新或者删除动作保存一行。这个方法可以当作对一个表的审计。下面显示了一个 PL/pgSQL 写的审计触发器过程的例子。

这个例子触发器保证了在 emp 表上的任何插入， 更新或者删除动作都被记录到了 emp_audit 表里（也就是，审计）。 当前时间和用户名会被记录到数据行里，以及还有执行的操作。

CREATE TABLE emp (

    empname   text NOT NULL,

    salary    integer

);

CREATE TABLE emp_audit(

    operation  char(1) NOT NULL,

    stamp timestamp NOT NULL,

    userid text NOT NULL,

    empname text NOT NULL,

    salary integer

);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS

emp_audit$

BEGIN

        --

        -- 在 emp_audit 里创建一行，反映对 emp 的操作，

        -- 使用特殊变量 TG_OP 获取操作类型。

        --

        IF (TG_OP = 'DELETE') THEN

            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

            RETURN OLD;

        ELSIF (TG_OP = 'UPDATE') THEN

            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;

            RETURN NEW;

        END IF;

        RETURN NULL; -- 忽略结果，因为它是个 AFTER 触发器

END;

$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()

触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表 — 通常可以大大缩小运行时间。 这个技巧经常用于数据仓库，这个时候，需要测量的表（叫事实表）可能会非常巨大。 下面演示了一个 PL/pgSQL 触发器过程的例子， 它为某个数据仓库的一个事实表维护一个概要表。 

下面的模式有一部分是基于 Ralph Kimball 的The Data Warehouse Toolkit 里面的 Grocery Store 例子。 

--

-- 主表 - 时间维以及销售事实。

--

CREATE TABLE time_dimension (

    time_key                    integer NOT NULL,

    day_of_week                 integer NOT NULL,

    day_of_month                integer NOT NULL,

    month                       integer NOT NULL,

    quarter                     integer NOT NULL,

    year                        integer NOT NULL

);

CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (

    time_key                    integer NOT NULL,

    product_key                 integer NOT NULL,

    store_key                   integer NOT NULL,

    amount_sold                 numeric(12,2) NOT NULL,

    units_sold                  integer NOT NULL,

    amount_cost                 numeric(12,2) NOT NULL

);

 CREATE INDEX sales_fact_time ON sales_fact(time_key);

--

-- 摘要表 - 根据时间的销售。

--

CREATE TABLE sales_summary_bytime (

    time_key                    integer NOT NULL,

    amount_sold                 numeric(15,2) NOT NULL,

    units_sold                  numeric(12) NOT NULL,

    amount_cost                 numeric(15,2) NOT NULL

);

CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--

-- 在 UPDATE，INSERT，DELETE 的时候根新概要字段的函数和触发器。

--

CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS 

$maint_sales_summary_bytime$

DECLARE

        delta_time_key          integer;

        delta_amount_sold       numeric(15,2);

        delta_units_sold        numeric(12);

        delta_amount_cost       numeric(15,2);

    BEGIN

        -- 计算增/减量。

        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;

            delta_amount_sold = -1 * OLD.amount_sold;

            delta_units_sold = -1 * OLD.units_sold;

            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- 禁止改变 time_key 的更新 -

            -- （可能并不是很强制，因为 DELETE + INSERT 是大多数可能

            -- 产生的修改）。

IF ( OLD.time_key != NEW.time_key) THEN

RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;

            END IF;

            delta_time_key = OLD.time_key;

            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;

            delta_units_sold = NEW.units_sold - OLD.units_sold;

            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;

            delta_amount_sold = NEW.amount_sold;

            delta_units_sold = NEW.units_sold;

            delta_amount_cost = NEW.amount_cost;

        END IF;

        -- 用新数值更新概要行。

        UPDATE sales_summary_bytime

            SET amount_sold = amount_sold + delta_amount_sold,

                units_sold = units_sold + delta_units_sold,

                amount_cost = amount_cost + delta_amount_cost

            WHERE time_key = delta_time_key;

        -- There might have been no row with this time_key (e.g new data!).

        IF (NOT FOUND) THEN

            BEGIN

                INSERT INTO sales_summary_bytime (

                            time_key,

                            amount_sold,

                            units_sold,

                            amount_cost)

                    VALUES (

                            delta_time_key,

                            delta_amount_sold,

                            delta_units_sold,

                            delta_amount_cost

                           );

            EXCEPTION

                --

                -- 捕获两个事务维一个新 time_key 增加数据的冲突条件

                --

                WHEN UNIQUE_VIOLATION THEN

                    UPDATE sales_summary_bytime

                        SET amount_sold = amount_sold + delta_amount_sold,

                            units_sold = units_sold + delta_units_sold,

                            amount_cost = amount_cost + delta_amount_cost

                        WHERE time_key = delta_time_key;

            END;

        END IF;

        RETURN NULL;

END;

$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime

AFTER INSERT OR UPDATE OR DELETE ON sales_fact

    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();