Postgresql 中文操作指南

43.11. PL/pgSQL under the Hood #

本节讨论一些实现细节，这些细节通常对 PL/pgSQL 用户了解非常重要。

43.11.1. Variable Substitution #

PL/pgSQL 函数中的 SQL 语句和表达式可以引用函数中的变量和参数。在后台，PL/pgSQL 用查询参数替换此类引用项。查询参数仅会被替换在语法允许的位置。如极端情况，请考虑以下示例的糟糕编程风格：

INSERT INTO foo (foo) VALUES (foo(foo));

foo 的第一次出现语法上必须是一个表名，所以它不会被替换，即使函数有一个名为 foo 的变量。第二次出现必须是该表的列名，所以它也不会被替换。同样，第三次出现必须是一个函数名，所以也不会被替换。只有最后一个出现可能是引用 PL/pgSQL 函数的变量的候选项。

理解此问题还有一种方法，即变量替换只能将数据值插入到 SQL 命令中；它不能动态更改命令引用的数据库对象。（如果您想执行此操作，则必须动态构建命令字符串，如 Section 43.5.4中所述。）

由于变量的名称在语法上与表列的名称没有区别，因此在也引用表的语句中可能会出现歧义：给定的名称是表示表列还是变量？我们把之前的示例改成

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 while none of your column names start with v_ ，那么不会发生冲突。

或者，您可以限定不明确的引用以使其明确。在上例中，_src.foo_将对表列进行明确引用。要创建一个对变量的明确引用，请在带标签的块中声明它并使用块的标签（参见 Section 43.2）。例如：

<<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 43.5.4中所示。

变量替换目前仅适用于_SELECT_、INSERT、UPDATE、DELETE_以及包含其中一个命令（例如_EXPLAIN_和_CREATE TABLE … AS SELECT）的命令，因为主 SQL 引擎只允许在这些命令中使用查询参数。要在其他语句类型（通常称为实用程序语句）中使用非常量名称或值，你必须将实用程序语句构造为一个字符串，然后_EXECUTE_它。

43.11.2. Plan Caching #

PL/pgSQL 解释器在首次调用函数（在每个会话中）时解析函数的源文本并生成一个内部二进制指令树。指令树完全翻译了 PL/pgSQL 语句结构，但不会立即翻译函数中使用的单个 SQL 表达式和 SQL 命令。

每次先在函数中执行每个表达式和 SQL 命令时，PL/pgSQL 解释器都会使用 SPI 管理器的 SPI_prepare 函数，解析并分析该命令以创建已准备好的语句。随后访问该表达式或命令时，将重新使用已准备好的语句。因此，具有很少访问条件代码路径的函数将永远不会产生分析从未在当前会话中执行的那些命令的开销。一个缺点是，在执行过程中，直到函数的该部分被访问到，才能检测到特定表达式或命令中的错误。（在初始解析过程中将检测到微不足道的语法错误，但任何更深层次的错误都将在执行前无法检测到。）

此外，PL/pgSQL（或者更确切地说，SPI 管理器）可以尝试缓存与任何特定已准备好的语句相关联的执行计划。如果不使用已缓存的计划，则在每次访问该语句时都会生成一个新的执行计划，并且可以将当前参数值（即 PL/pgSQL 变量值）用于优化所选计划。如果该语句没有参数，或被执行多次，SPI 管理器会考虑创建一个与特定参数值无关的 generic 计划，并对其进行缓存以备重新使用。通常，这种情况只会发生在执行计划不会极度受其中引用的 PL/pgSQL 变量值影响时。如果是这样，每次生成一个计划都是净胜利。参见 PREPARE ，以获取关于已准备好的语句的行为的更多信息。

由于 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'应该解释为_timestamp，因为_logtable_的目标列是那种类型。因此，当_INSERT_被分析时，_'now'_将被转换为_timestamp_常量，然后在该会话的生命周期内_logfunc1_的所有调用中使用。不用说，这不是程序员想要的。一个更好的主意是使用_now()_或_current_timestamp_函数。

对于_logfunc2_，PostgreSQL 主解析器不知道_'now'_应该变成什么类型，因此它返回包含字符串_now_的类型为_text_的数据值。在接下来的对局部变量_curtime_的赋值中，PL/pgSQL 解析器通过调用_textout_和_timestamp_in_函数进行转换，将此字符串强制转换为_timestamp_类型。因此，计算出的时间戳会像程序员期望的那样在每次执行时更新。即使这恰好按预期工作，也不是很有效，因此最好还是使用_now()_函数。