40.10. 在后台下的PL/pgSQL · PostgreSQL 中文文档 9.3

# 40.10\. 在后台下的PL/pgSQL 本节讨论PL/pgSQL用户知道的比较重要的一些实现细节。 ## 40.10.1\. 变量替换在PL/pgSQL函数内的SQL语句和表达式可以参考变量和函数的参数。在后台，PL/pgSQL替代这些参考查询参数。参数只会按照句法允许一个参数或列引用的地方被取代。作为一个极端的例子，考虑不好的编程风格的这个例子： ``` INSERT INTO foo (foo) VALUES (foo); ``` `foo`第一次出现一定在语法上是表名字，所以它不会被取代，即使函数有一个可变名 `foo`。第二次发生必须是表列名称，所以它也不会被取代。只有第三次发生是参考函数变量的一个候选。 > **Note:** 9.0之前的PostgreSQL版本可能尝试所有三种情况中替换变量，导致语法错误。由于变量的名字语法上和表列名字没有什么不同，参考表的语句中有模糊：它是一个给定的名字意味着引用一个表列，或一个变量？让我们改变以往的例子 ``` INSERT INTO dest (col) SELECT foo + bar FROM src; ``` 在这里，`dest`和`src`必须是表名，并且 `col`必须是`dest`的列，但是`foo` 和`bar`可能是函数变量或者`src`的列。默认情况下，如果在一个SQL语句中的名字可以参考一个变量或表列，则PL/pgSQL将报告错误。你可以通过重命名变量或列，或限定不明确的引用，或者告诉PL/pgSQL 说明更喜欢哪个来解决这样的问题。最简单的解决方案是重命名变量或列。一个常见的编码规则是使用 PL/pgSQL变量的不同命名惯例而不是你使用列名称。比如，如果你一贯地命名函数变量`v_``_something_`，然而你的列没有以`v_`开头命名，不会发生冲突。另外你可以限定含糊的引用以使得它们明确。在上面的例子中，`src.foo`将是表列的明确参考。为了创建明确的引用变量，在标记块声明它并且使用块标签（参阅[Section 40.2](#calibre_link-859)）。比如， ``` <<block>> DECLARE foo int; BEGIN foo := ...; INSERT INTO dest (col) SELECT block.foo + bar FROM src; ``` 这里`block.foo`意味着变量，即使在`src`中有一列`foo`。函数的参数，以及特殊变量如`FOUND`，可以满足函数的名字，因为他们在使用函数名标记的外部块中隐式声明。有时修复在PL/pgSQL编码主体下所有不明确的引用是不切实际的。在这种情况下，你可以指定PL/pgSQL应该解决不明确的引用，作为变量（即兼容PostgreSQL 9.0之前的PL/pgSQL的行为），或作为表列（与其他一些系统兼容，如Oracle）要改变在系统范围基础上的这种行为，设置配置参数`plpgsql.variable_conflict`为 `error`, `use_variable`或者 `use_column`之一（`error`是出厂缺省值）。此参数会影响PL/pgSQL函数中语句后续的编译，但不是在当前会话中已编译的语句。由于更改此设置可以导致PL/pgSQL函数行为意想不到的变化，它只能由超级用户改变。你也可以在功能分析的基础上设定行为，通过在函数文本的开始处插入这些特殊的命令之一： ``` #variable_conflict error #variable_conflict use_variable #variable_conflict use_column ``` 这些命令只影响写入的函数，并且重写`plpgsql.variable_conflict`的设置。例如： ``` CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ #variable_conflict use_variable DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = curtime, comment = comment WHERE users.id = id; END; $$ LANGUAGE plpgsql; ``` 在`UPDATE`命令中，`curtime`, `comment`, 和`id`将引用函数的变量和`users`是否具有这些名称列的参数。请注意我们必须限定到`WHERE`子句`users.id`的引用以使得它引用表列。我们没有必要限定引用到`comment`作为`UPDATE`列表中的目标，因为语法上必定是`users`的列。我们可以写不依赖于这种方式的`variable_conflict`设置的同样函数： ``` CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ <<fn>> DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment WHERE users.id = stamp_user.id; END; $$ LANGUAGE plpgsql; ``` 变量代换不会发生在给定`EXECUTE`或者它的变种之一的命令字符串中。如果你需要插入一个不同的值到这个命令中，执行它作为构建字符串值的一部分，或使用`USING`，正如 [Section 40.5.4](#calibre_link-1590)说明的。变量替换目前只能在`SELECT`,`INSERT`, `UPDATE`和 `DELETE`命令中运行，因为主要的SQL引擎允许这些命令中的查询参数。为了使用其他语句类型中非恒定的名称或值（一般称为实用语句），你必须构建实用语句作为字符串并且`EXECUTE`它。 ## 40.10.2\. 计划缓存 PL/pgSQL解释器解析函数的源文本并且第一次函数被调用时（每个会话中）产生一个内部二进制指令树。指令树充分翻译PL/pgSQL语句结构，但个别SQL表达式和在函数中使用的SQL命令不是立即翻译。在函数中首先执行每个表达式和SQL命令，PL/pgSQL 解释器解析并且分析命令以创建预备语句，使用SPI管理的 `SPI_prepare`函数。随后访问表达式或命令重新使用事先准备好的语句。因此，带有条件编码路径的函数很少被访问将不会产生分析不在当前会话中执行的命令的开销，一个缺点是在一个特定的表达或命令中的错误不能被检测到直到执行达到函数部分（琐碎的语法错误在初步解析传递期间将被检测到，但是任何更深的东西将不会被检测到直到执行为止。） PL/pgSQL（或者更准确的说，SPI管理者）可以尝试与任何特别已准备语句相关的缓存执行计划。如果没有使用缓存计划，那么在每次访问语句中产生一个新的执行计划，并且当前的参数值（即，PL/pgSQL 变量值）可以用来优化选择方案。如果语句没有参数，或是执行多次，SPI管理者将考虑创建_generic_计划不依赖于特定的参数值，并且缓存再利用。只有执行计划对PL/pgSQL变量中引用的值不太敏感时，往往会发生。如果是，每次生成一个计划都是净赢。参阅[PREPARE](#calibre_link-625)获得更多有关预备语句行为信息。因为PL/pgSQL保存已预备好语句并且有时以这种方式执行计划，直接出现在PL/pgSQL函数中的SQL命令必须查阅相同表和每个执行列；也就是说，你不能使用参数作为SQL命令的表或列的名字。为了应对这个限制，你可以使用PL/pgSQL `EXECUTE` 语句构建动态命令；以执行新的解析分析和每个执行上构建新的执行计划为代价。记录变量的可变性质提出连接中的另一个问题。当在表达式或语句中使用记录变量字段时，该字段数据类型必须不能从函数的一个调用到下一个改变，因为当表达式第一个到达时使用目前数据类型分析每个表达式。 `EXECUTE`必要时可以用于解决这个问题。如果相同函数作为多个表的触发器使用， PL/pgSQL为了每个表独立地准备并且缓存声明；即，有一个触发器函数和表组合的高速缓存，而不只是为每个函数。这解决了一些数据类型不同的问题；例如，一个触发器函数可以使用命名`key`的列成功运行，即使发生不同的表中有不同的类型。同样，具有多态性参数类型的函数有一个他们被调用的实际参数类型的每个组合的单独声明缓存，所以该数据类型差异不会导致意外失败。语句缓存有时会对时间敏感值的解释有令人惊讶的影响。例如在这两个函数要做的内容之间有区别： ``` CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$ BEGIN INSERT INTO logtable VALUES (logtxt, 'now'); END; $$ LANGUAGE plpgsql; ``` 和 ``` CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$ DECLARE curtime timestamp; BEGIN curtime := 'now'; INSERT INTO logtable VALUES (logtxt, curtime); END; $$ LANGUAGE plpgsql; ``` 在`logfunc1`的情况下，该 PostgreSQL主解析器知道当分析`INSERT`时字符串`'now'`应解释为`时间戳`，因为`logtable`目标列是那种类型。因此，当分析`INSERT`时， `'now'`将被转换为`timestamp`常量，然后在会话的整个生命周期中用于`logfunc1`的所有调用。不用说，这不是程序员希望的。一个更好的办法是使用`now()`或者`current_timestamp`函数。在`logfunc2`的情况下， PostgreSQL主解析器并不知道 `'now'`应该成为什么类型，因此它返回包含字符串`now` 类型`text`的数据值。随后分配给局部变量`curtime`期间， PL/pgSQL解析器通过调用`text_out`和 `timestamp_in`转换函数将这个字符串转换为`timestamp`类型，因此，作为编程期望每次执行时更新计算时间戳。尽管这正如预期的那样发生，这不是非常有效的，所以`now()`函数的使用仍然会是一个更好的主意。