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

引号处理：

·