42.10. PL/pgSQL 深入研究

• 42 .10.1. 可变替代

• 42 .10.2. 计划缓存

本节讨论一些实现细节，对于 PL/pgSQL 用户而言，这些细节通常很重要。

42 .10.1. 可变替代

PL/pgSQL 函数中的 SQL 语句和表达式可以引用函数的变量和参数。在后台，PL/pgSQL 将查询参数替换为此类引用。仅在语法上允许使用参数或列引用的位置替换参数。作为一个极端的情况，请考虑以下不良编程风格的示例：

INSERT INTO foo (foo) VALUES (foo);

从句法上讲，第一次出现的foo必须是一个表名，因此即使该函数具有名为foo的变量，它也不会被替换。第二次出现必须是表的列名，因此也不会被替换。只有第三次出现才可以引用该函数的变量。

由于变量的名称在语法上与表列的名称没有区别，因此在引用表的语句中可能存在歧义：给定名称是要引用表列还是变量？让我们将前面的示例更改为

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 42.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命令中，users是否具有这些名称的列，curtime，comment和id将引用函数的变量和参数。注意，我们必须限定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 42.5.4所示。

当前，变量替换仅在SELECT，INSERT，UPDATE和DELETE命令中起作用，因为主 SQL 引擎仅在这些命令中允许查询参数。要在其他语句类型(通常称为 Util 语句)中使用非常量名称或值，必须将 Util 语句构造为字符串，并EXECUTE。

42 .10.2. 计划缓存

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

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

PL/pgSQL(或更准确地说，SPIManagement 器)还可以尝试缓存与任何特定的准备好的语句关联的执行计划。如果未使用缓存的计划，则每次访问该语句时都会生成一个新的执行计划，并且可以使用当前参数值(即 PL/pgSQL 变量值)来优化所选计划。如果该语句没有参数，或者执行了多次，则 SPIManagement 器将考虑创建一个不依赖于特定参数值的“通用”计划，并将其缓存以供重用。通常，只有当执行计划对其中引用的 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;

and:

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 解释器通过调用text_out和timestamp_in函数进行转换，将该字符串转换为timestamp类型。因此，计算出的时间戳将按照程序员的期望在每次执行时进行更新。即使这可以按预期工作，但效率并不高，因此使用now()函数仍然是一个更好的主意。