PostgreSQL - PL/pgSQL - SQL 过程语言

PL/pgSQL　是　Postgres　数据库系统的一个可装载的过程语言．　
这个包最初是　Jan　Wieck　写的．　
概述
PL/pgSQL　的设计目标是创建一种可装载的过程语言，可以　
可用于创建函数和触发器过程，　
为　SQL　语言增加控制结构，　

可以执行复杂的计算，　

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

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

容易使用．

PL/pgSQL　函数第一次被后端调用时，PL/pgSQL　的调用控制器分析函数源文本生成二进制指令树．所生成的字节码在调用控制器里是用函数的对象标识来标记的．这样就保证如果使用　DROP/CREATE　序列修改了函数，不需要建立一个新的数据库联接就能令修改生效．　
对于函数里用到的所有表达式和　SQL　语句，PL/pgSQL　字节码解析器使用　SPI　管理器　SPI_prepare()　和　SPI_saveplan()　函数创建一个准备好了的执行规划．这些动作是在（解析器）第一次处理时完成的，独立的语句是在　PL/pgSQL　函数里面处理的．因此，一个带有条件代码的有许多语句的需要执行规划的函数，将只准备和保存那些在整个数据库联接周期内真正使用到的规划．　

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


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

描述
PL/pgSQL　的结构
PL/pgSQL　语言是大小写不敏感的语言．所有关键字和标识都可以混合大小写使用．　
PL/pgSQL　是一种面向块的语言．一个块象下面这样定义　

[<>]
[DECLARE
　　　　declarations]
BEGIN
　　　　statements
END;
在一个语句块（statement　section）里面可以有任意个子语句块．子语句块可以用于屏蔽语句块外面的变量．在语句块前面的声明段（declarations　section）里定义的变量在每次进入语句块时都初始化为它们的缺省值，而不是每次函数调用时初始化一次．　
我们一定不要把　PL/pgSQL　里的分组语句　BEGIN/END　和用于事务控制的数据库命令搞混了．函数和触发器不能开始或提交一个事务并且Postgres　也没有嵌套事务的用法．

注释
在　PL/pgSQL　里有两种类型的注释．双破折号　'--'　引出到该行行尾的单行注释．一个　'/*'　开始一个块注释，一直延伸到下一个　'*/'出现．块注释不能嵌套使用，但是双破折号注释可以封装在一个块注释里面并且双破折号注释可以隐藏一个块注释分隔符　'/*'　和　'*/'．
声明
在一个语句块或者它的子语句块里用到的所有变量，行和记录都必须在一个语句块的声明段里定义－－　除了　FOR　循环里说明循环范围的整数循环变量以外．赋予一个　PL/pgSQL　函数的参数自动用常用的标识　$n　定义．定义有下面语法：　
name　[　CONSTANT　]　>type>　[　NOT　NULL　]　[　DEFAULT　|　:=　value　];　
　　
　　

定义一个指明了的基本类型的变量．如果变量被定义为　CONSTANT，该变量值就不能修改．如果声明了　NOT　NULL，则赋予一个　NULL　值给该变量将导致一个运行时错误．因为所有变量的缺省值都是　SQL　NULL，因而所有定义为　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　对那些数值正确解释的唯一方法．　

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

语句
任何象下面声明的　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　）里面，“%”　用做一个后面跟随的逗号分隔的标识的占位符．可能的级别（level）是　DEBUG　（安静地停止运行的数据库），NOTICE　（向数据库书写日志并向前端客户应用发送信息）和　EXCEPTION　（向数据库书写日志并且退出事务　）．　
　　
条件　
IF　expression　THEN
　　　　statements
[ELSE
　　　　statements]
END　IF;
表达式　expression　必须返回一个最起码可以转换成布尔类型的数值．　
　　
循环　
　　
　　

有许多循环的类型．　

[<>]
LOOP
　　　　statements
END　LOOP;
一个无条件循环必须用一条　EXIT　语句显式终止．可选的标记　label　可以被　EXIT　用于嵌套循环用于声明应该结束哪一层循环．　
[<>]
WHILE　expression　LOOP
　　　　statements
END　LOOP;
一个条件循环在表达式　expression　为真时执行．　
[<>]
FOR　name　IN　[　REVERSE　]　expression　..　expression　LOOP
　　　　statements
END　LOOP;
一个在某一整数范围内执行的循环，变量　name　自动做为整数类型创建，并且只存在于循环内部．两个表达式给出循环范围的区间，它们只是在进入循环后才被计算出数值．循环步进值总是　1．　
[<>]
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
　　　　　　　　--　检查是否给出了　empname　和　salary
　　　　　　　　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;

　　　　　　　　--　我们必须付帐给谁？
　　　　　　　　IF　NEW.salary　<　0　THEN
　　　　　　　　　　　　RAISE　EXCEPTION　''%　cannot　have　a　negative　salary'',　NEW.empname;
　　　　　　　　END　IF;

　　　　　　　　--　记住何时何人的薪水被修改了
　　　　　　　　NEW.last_date　:=　''now'';
　　　　　　　　NEW.last_user　:=　getpgusername();
　　　　　　　　RETURN　NEW;
　　　　END;
'　LANGUAGE　'plpgsql';

CREATE　TRIGGER　emp_stamp　BEFORE　INSERT　OR　UPDATE　ON　emp
　　　　FOR　EACH　ROW　EXECUTE　PROCEDURE　emp_stamp();