Postgresql 中文操作指南

43.5. Basic Statements #

在本节和以下各节中，我们将描述 PL/pgSQL 明确理解的所有语句类型。任何不被识别为这些语句类型之一的语句都被假定为 SQL 命令，并会按 Section 43.5.2中所述发送到主数据库引擎执行。

43.5.1. Assignment #

向 PL/pgSQL 变量赋值时，应编写为：

variable { := | = } expression;

如前所述，此类语句中的表达式通过发送到主数据库引擎的 SQL SELECT 命令进行评估。表达式必须产生一个单一值（如果变量是行或记录变量，则可能是行值）。目标变量可以是简单变量（可以选择用块名称限定）、行或记录目标的字段，或者数组目标的元素或切片。可以使用 Equal (=) 代替兼容 PL/SQL 的 :=。

如果表达式的结果数据类型与变量的数据类型不匹配，则该值将被强制转换，就像一个赋值强制转换（请参见 Section 10.4）。如果不知道涉及的数据类型对的赋值强制转换，则 PL/pgSQL 解释器将尝试按文本形式转换结果值，即应用结果类型输出函数，然后应用变量类型输入函数。请注意，如果输入函数无法接受结果值的字符串形式，可能会导致输入函数生成运行时错误。

示例：

tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;

43.5.2. Executing SQL Commands #

一般来说，可以通过写入命令在 PL/pgSQL 函数中执行不返回行的任何 SQL 命令。例如，可以通过编写来创建和填充表

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

如果命令确实返回行（例如，使用 SELECT 的 INSERT 或使用 RETURNING 的 INSERT/UPDATE/DELETE），那么有两种处理方式。当命令最多返回一行时，或者当您仅关心输出中的第一行时，像往常一样编写命令，但添加一个 INTO 子句来捕捉输出，如 Section 43.5.3 所述。要处理所有输出行，像 Section 43.6.6 所述，编写命令作为 FOR 循环的数据源。

通常，仅执行静态定义的 SQL 命令是不够的。通常，你希望命令使用不同的数据值，甚至是通过在不同时间使用不同的表名等更基本的方式进行变化。同样，根据实际情况，有两种方法可以继续执行。

PL/pgSQL 变量值可以自动插入到可优化 SQL 命令中，这些命令包括 SELECT、INSERT、UPDATE、DELETE、MERGE，以及包含其中之一的某些实用程序命令，例如 EXPLAIN 和 CREATE TABLE … AS SELECT。在这些命令中，出现在命令文本中的任何 PL/pgSQL 变量名称都在运行时被查询参数所替代，然后变量的当前值作为参数值提供。这与前面针对表达式所述的处理过程完全相同；有关详细信息，请参见 Section 43.11.1。

通过这种方式执行优化 SQL 命令时，PL/pgSQL 可能会缓存并重复使用命令的执行计划，如 Section 43.11.2 中所述。

不可优化的 SQL 命令（也称为实用程序命令）无法接受查询参数。因此，PL/pgSQL 变量的自动替换不适用于此类命令。要将非常量文本包含在从 PL/pgSQL 执行的实用程序命令中，您必须将实用程序命令构建为字符串，然后 EXECUTE 它，如 Section 43.5.4 中所述。

如果您希望除了提供数据值外还以其他方式修改命令，例如更改表名称，也务必使用 EXECUTE。

有时评估表达式或 SELECT 查询却丢弃结果是有用的，例如在调用具有副作用但无有用结果值时。要在 PL/pgSQL 中执行此操作，请使用 PERFORM 语句：

PERFORM query;

这将执行 query 并丢弃结果。编写 query 的方式与编写 SQL SELECT 的方式相同，但是将初始关键字 SELECT 替换为 PERFORM。对于 WITH 查询，请使用 PERFORM，然后将查询括在圆括号中。（在这种情况下，查询只能返回一行。）PL/pgSQL 变量将按上述方式替换到查询中，并且将计划按相同的方式缓存。此外，如果查询产生了至少一行，则将特殊变量 FOUND 设置为真，如果它没有产生行，则设置成假（参见 Section 43.5.5）。

Note

人们可能会希望直接编写 SELECT 来实现此结果，但目前唯一可接受的方法是 PERFORM。可以返回行的 SQL 命令（例如 SELECT），除非它具有 INTO 子句（如下一节所述），否则会被拒绝为错误。

示例：

PERFORM create_mv('cs_session_page_requests_mv', my_query);

43.5.3. Executing a Command with a Single-Row Result #

生成单行的（可能是多列的）SQL 命令的结果可以分配给记录变量、行类型变量或标量变量列表。这是通过编写基本 SQL 命令并添加 INTO 子句来实现的。例如，

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

其中 target 可以是记录变量、行变量或按逗号分隔的简单变量和记录/行字段列表。将按上述方式将 PL/pgSQL 变量替换到命令的其余部分（即 INTO 子句之外的部分），并将计划以相同的相同方式缓存。这对带有 RETURNING 的 SELECT、INSERT/UPDATE/DELETE 和返回行集的特定实用命令（如 EXPLAIN）有效。除了 INTO 子句以外，SQL 命令与在 PL/pgSQL 外部编写的方式相同。

Tip

请注意，此 SELECT 和 INTO 的解释与 PostgreSQL 的常规 SELECT INTO 命令完全不同，其中 INTO 目标是新创建的表。如果您想在 PL/pgSQL 函数内从 SELECT 结果创建表，请使用 CREATE TABLE … AS SELECT 语法。

如果行变量或变量列表用作目标，则命令的结果列必须与目标的数量和数据类型完全匹配，否则会导致运行时错误。当记录变量是目标时，它会自动配置到命令结果列的行类型。

INTO 子句几乎可以在 SQL 命令中的任何位置出现。通常，它在 SELECT 命令的 select_expressions 列表前或后书写一次，或在其他命令类型命令的末尾书写一次。务必遵循此规则，以免未来的版本中 PL/pgSQL 解析器更加严格。

如果在 INTO 子句中未指定 STRICT，则 target 将设置成该命令返回的第一行，或者如果命令没有返回行，则将其设置成 null。（请注意，“第一行” 的定义不明确，除非您使用了 ORDER BY。）第一行之后的所有结果行都会被丢弃。您可以检查特殊 FOUND 变量（参见 Section 43.5.5）来确定是否返回了一行：

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

如果指定 STRICT 选项，则命令必须完全返回一行，否则将报告运行时错误，可能是 NO_DATA_FOUND（没有行）或 TOO_MANY_ROWS（多行）。如果您希望捕捉该错误， можете使用一个异常块，例如：

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

在使用 STRICT 执行命令成功后，将始终将 FOUND 设为 true。

对于带有 RETURNING 的 INSERT/UPDATE/DELETE，即使未指定 STRICT，PL/pgSQL 也会对多行返回报告错误。这是因为没有诸如 ORDER BY 等选项来确定应该返回哪一行。

如果函数启用了 print_strict_params，则当由于不满足 STRICT 的要求而抛出错误时，错误消息的 DETAIL 部分将包含有关传递给命令的参数的信息。您可以通过设置 plpgsql.print_strict_params 更改所有函数的 print_strict_params 设置，但仅会影响后续函数编译。您还可以使用编译器选项逐个函数启用它，例如：

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

在失败的情况下，此函数可能会生成如下错误消息：

ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

Note

STRICT 选项匹配 Oracle PL/SQL 的 SELECT INTO 和相关语句的行为。

43.5.4. Executing Dynamic Commands #

通常，您希望在 PL/pgSQL 函数中生成动态命令，即在每次执行时都会涉及不同的表或不同数据类型的命令。PL/pgSQL 正常尝试缓存命令的计划（如 Section 43.11.2 中所述）在这样的场景中不起作用。要处理此类问题，提供了 EXECUTE 语句：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

其中 command-string 是生成包含要执行的命令的字符串（类型 text）的表达式。可选 target 是记录变量、行变量或按逗号分隔的简单变量和记录/行字段列表，将命令的结果存储到其中。可选 USING 表达式提供要插入到命令中的值。

PL/pgSQL 变量未在计算的命令字符串上进行任何替换。任何必需的变量值必须在构造命令字符串时插入其中；或者您可以按如下所述使用参数。

另外，通过 EXECUTE 执行的命令没有计划缓存。相反，每次运行语句时都会始终规划命令。因此，可以在函数内动态创建命令字符串，以便对不同的表和列执行操作。

INTO 子句指定应将返回行的 SQL 命令的结果分配到何处。如果提供了行变量或变量列表，则它必须与命令结果的结构完全匹配；如果提供了记录变量，它会自动配置自己以匹配结果结构。如果返回了多行，则只将第一行分配给 INTO 变量。如果没有返回任何行，则向 INTO 变量分配 NULL。如果没有指定 INTO 子句，则会丢弃命令结果。

如果给定了 STRICT 选项，则会报告一个错误，除非命令准确地生成一行。

命令字符串可以使用参数值，在命令中引用为 $1、$2 等。这些符号引用 USING 子句中提供的的值。这种方法通常优于将数据值作为文本插入到命令字符串中：它避免了将值转换为文本的运行时开销，并且非常不容易出现 SQL 注入攻击，因为不需要引用或转义。一个例子是：

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

请注意，参数符号只能用于数据值，如果您想使用动态确定的表或列名，则必须将它们作为文本插入到命令字符串中。例如，如果需要根据动态选择的表来执行上述查询，则可以执行以下操作：

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

一种更简洁的方法是使用 format() 的 %I 规范，使用自动引用来插入表或列名：

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

（此示例依赖于 SQL 规则，即由换行符分隔的字符串文字会隐式连接。）

对参数符号的另一个限制是，它们只适用于可优化 SQL 命令（SELECT、INSERT、UPDATE、DELETE、MERGE，以及包含其中一个命令的某些命令）。在其他语句类型中（通常称为实用程序语句），您必须以文本方式插入值，即使它们只是数据值。

EXECUTE，带有简单的常量命令字符串和一些 USING 参数，如上面第一个示例中，在功能上等同于直接在 PL/pgSQL 中编写命令并允许 PL/pgSQL 变量替换自动发生。重要的区别在于 EXECUTE 将在每次执行时重新规划命令，生成一个特定于当前参数值的计划；而 PL/pgSQL 可能创建通用计划并将其缓存以供重用。在最佳计划严重依赖于参数值的情况下，使用 EXECUTE 可以帮助确认未选择通用计划。

SELECT INTO 目前不受 EXECUTE 支持；相反，执行一个简单的 SELECT 命令，并指定 INTO 作为 EXECUTE 的一部分。

Note

PL/pgSQL EXECUTE 语句与 PostgreSQL 服务器支持的 EXECUTE SQL 语句无关。服务器的 EXECUTE 语句无法直接在 PL/pgSQL 函数中使用（并且不需要）。

Example 43.1. Quoting Values in Dynamic Queries

在使用动态命令时，您经常必须处理单引号转义。在函数主体中引用固定文本的建议方法是美元引用。（如果您有未使用美元引用的旧代码，请参阅 Section 43.12.1 中的概览，这可以在将上述代码转换为更合理的方案时节省您的精力。）

动态值需要谨慎处理，因为它们可能包含引号字符。使用 format() 的示例（假设您正在使用美元引用函数正文，因此无需重复引号）：

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

也可以直接调用引用函数：

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

此示例演示了如何使用 quote_ident 和 quote_literal 函数（参见 Section 9.4）。为了安全起见，在插入动态查询之前，应该通过 quote_ident 传递包含列或表标识符的表达式。应该通过 quote_literal 传递在构造的命令中应包含字面字符串的值的表达式。这些函数分别采取适当的步骤来返回用双引号或单引号引起来输入文本，并正确转义所有嵌入的特殊字符。

因为 quote_literal 被标记为 STRICT，所以当使用 null 参数调用时它将始终返回 null。在上面的示例中，如果 newvalue 或 keyvalue 为 null，则整个动态查询字符串将变为 null，从而导致 EXECUTE 出错。您可以通过使用 quote_nullable 函数来避免此问题，该函数与 quote_literal 的工作方式相同，只是在使用 null 参数调用时它返回字符串 NULL。例如，

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

如果您处理的值可能为 null，则通常应该使用 quote_nullable 替换 quote_literal。

与往常一样，必须小心确保查询中的 null 值不会产生意外结果。例如 WHERE 子句

'WHERE key = ' || quote_nullable(keyvalue)

如果 keyvalue 为 null，则永远不会成功，因为使用等号运算符 = 和 null 操作数，其结果永远为 null。如果您希望 null 像普通键值一样工作，那么您需要将上面重写为：

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

（目前，与 = 相比，IS NOT DISTINCT FROM 的处理效率低得多，因此除非必要，否则请不要这样做。有关空值和 IS DISTINCT 的详细信息，请参见 Section 9.2。）

请注意，美元引用仅可用于引用固定文本。尝试将此示例写成如下形式是一个非常糟糕的想法：

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

因为如果碰巧 newvalue 的内容包含 $$，它将会中断。您可能会选择的任何其他美元引用定界符都会遇到相同的反对意见。因此，为了安全地引用事先未知的文本，您 must 会根据具体情况使用 quote_literal、quote_nullable 或 quote_ident。

还可以使用 format 函数（请参见 Section 9.4.1）安全地构造动态 SQL 语句。例如：

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

%I 等效于 quote_ident，%L 等效于 quote_nullable。format 函数可与 USING 子句结合使用：

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

此种形式更好，因为变量以其原生数据类型格式进行处理，而不是无条件地将它们转换为文本并通过 %L 对它们进行引用。它也更有效。

可以在 Example 43.10 中看到一个更大的动态命令示例和 EXECUTE，它构建并执行一个 CREATE FUNCTION 命令来定义一个新函数。

43.5.5. Obtaining the Result Status #

有几种方法可以确定命令的效果。第一种方法是使用 GET DIAGNOSTICS 命令，其形式为：

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

此命令允许检索系统状态指示符。CURRENT 是一个噪音词（但在 Section 43.6.8.1 中也参见 GET STACKED DIAGNOSTICS）。每个 item 都是一个关键词，用于识别要分配给指定 variable（其应为可以接收它的正确数据类型）的状态值。 Table 43.1 中显示了当前可用的状态项。可以使用冒号等于 (:=) 来代替 SQL 标准 = 令牌。一个示例：

GET DIAGNOSTICS integer_var = ROW_COUNT;

Table 43.1. Available Diagnostics Items

Name

Type

Description

ROW_COUNT

bigint

最近的 SQL 命令处理的行数

PG_CONTEXT

text

描述当前调用堆栈的文本行（请参见 Section 43.6.9 ）

PG_ROUTINE_OID

oid

当前函数的 OID

确定命令效果的第二种方法是检查一个名为 FOUND 的特殊变量，其类型为 boolean。在每个 PL/pgSQL 函数调用中，FOUND 起始值为 false。它由以下类型的每个语句设置：

其他 PL/pgSQL 语句不会改变 FOUND 的状态。特别要注意，EXECUTE 更改 GET DIAGNOSTICS 的输出，但不会更改 FOUND。

FOUND 是每个 PL/pgSQL 函数中的局部变量；对其进行的任何更改只会影响当前函数。

43.5.6. Doing Nothing At All #

有时，一个什么都不做的占位语句很有用。例如，它可以指示一个 if/then/else chain 的一条分支是故意留空的。为此，请使用 NULL 语句：

NULL;

例如，以下两个代码段等效：

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignore the error
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ignore the error
END;

哪一个更好取决于个人喜好。

Note

在 Oracle 的 PL/SQL 中，不允许空语句列表，因此 NULL 语句对于此类情况 required。PL/pgSQL 允许您只不写任何内容。