PostgreSQL 教程

Appearance

43.5 基本语句# ​

43.5.1. 赋值
43.5.2. 执行 SQL 命令
43.5.3. 执行带单行结果的命令
43.5.4. 执行动态命令
43.5.5. 获取结果状态
43.5.6. 无操作

在本节和后续章节中，我们将描述PL/pgSQL明确理解的所有语句类型。任何未被识别为这些语句类型之一的内容都将被视为 SQL 命令，并被发送到主数据库引擎执行，如第 43.5.2 节中所述。

43.5.1. 赋值# ​

将值赋给PL/pgSQL变量的写法如下：

variable { := | = } expression;

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

如果表达式的结果数据类型与变量的数据类型不匹配，则该值将被强制转换，就像赋值转换一样（请参见第 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. 执行 SQL 命令# ​

通常，任何不返回行的 SQL 命令都可以通过编写该命令在PL/pgSQL函数中执行。例如，您可以通过编写以下内容来创建并填充一个表：

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

如果该命令确实返回行（例如SELECT，或INSERT/UPDATE/DELETE以及RETURNING），则有两种处理方法。当该命令最多返回一行，或者您只关心输出的第一行时，请像往常一样编写该命令，但添加一个INTO子句来捕获输出，如第 43.5.3 节中所述。要处理所有输出行，请将该命令作为FOR循环的数据源编写，如第 43.6.6 节中所述。

通常，仅执行静态定义的 SQL 命令是不够的。通常，您希望命令使用不同的数据值，甚至以更基本的方式进行更改，例如在不同时间使用不同的表名。同样，根据情况有两种方法可以进行。

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

以这种方式执行可优化的 SQL 命令时，PL/pgSQL可能会缓存并重新使用该命令的执行计划，如第 43.11.2 节中所述。

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

如果您想通过提供数据值以外的方式修改命令，例如更改表名，也必须使用EXECUTE。

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

PERFORM query;

这将执行*query并丢弃结果。编写query*的方式与编写 SQLSELECT命令的方式相同，但将初始关键字SELECT替换为PERFORM。对于WITH查询，请使用PERFORM，然后将查询放在括号中。（在这种情况下，查询只能返回一行。）PL/pgSQL变量将如上所述替换到查询中，并且计划将以相同的方式缓存。此外，如果查询至少产生一行，则特殊变量FOUND设置为 true，如果没有产生任何行，则设置为 false（请参见第 43.5.5 节）。

注意

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

一个示例

PERFORM create_mv('cs_session_page_requests_mv', my_query);

43.5.3. 执行具有单行结果的命令# ​

产生单行（可能有多个列）的 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子句之外的所有内容），就像上面描述的那样，并且计划将以相同的方式缓存。这适用于SELECT、INSERT/UPDATE/DELETE（带RETURNING）以及某些返回行集的实用程序命令，如EXPLAIN。除了INTO子句之外，SQL 命令与在PL/pgSQL外部编写时的相同。

提示

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

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

INTO子句几乎可以出现在 SQL 命令中的任何位置。通常，它在SELECT命令中*select_expressions*列表的正前方或正后方编写，或者在其他命令类型的末尾编写。建议你遵循此约定，以防PL/pgSQL解析器在未来版本中变得更加严格。

如果STRICT未在INTO子句中指定，则*target*将被设置为该命令返回的第一行，或者如果该命令未返回任何行，则设置为 null。（请注意，除非您使用了ORDER BY，否则““第一行””未明确定义。）第一行之后的任何结果行将被丢弃。您可以检查特殊FOUND变量（参见第 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

注意

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

43.5.4. 执行动态命令# ​

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

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

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

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

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

INTO子句指定应将返回行的 SQL 命令的结果分配到何处。如果提供了行变量或变量列表，则它必须与命令结果的结构完全匹配；如果提供了记录变量，它将自动配置自身以匹配结果结构。如果返回多行，则仅第一行将分配给INTO变量。如果没有返回任何行，则将 NULL 分配给INTO变量。如果没有指定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以及包含其中一个命令的某些命令)。在其他语句类型（通常称为实用程序语句）中，即使它们只是数据值，也必须以文本方式插入值。

使用简单常量命令字符串和一些USING参数的EXECUTE（如上例所示）在功能上等同于直接在PL/pgSQL中编写命令并允许自动替换PL/pgSQL变量。重要的区别在于EXECUTE将在每次执行时重新计划命令，生成针对当前参数值而定制的计划；而PL/pgSQL可能会创建通用计划并将其缓存以供重复使用。在最佳计划极度依赖于参数值的情况下，使用EXECUTE来确保不选择通用计划会很有帮助。

SELECT INTO当前在EXECUTE中不受支持；而应执行一个简单的SELECT命令，并指定INTO作为EXECUTE本身的一部分。

注意

PL/pgSQLEXECUTE语句与PostgreSQL服务器支持的EXECUTESQL 语句无关。服务器的EXECUTE语句不能直接在PL/pgSQL函数中使用（也不需要）。

示例 43.1. 在动态查询中引用值

使用动态命令时，通常需要处理单引号的转义。在函数主体中引用固定文本的推荐方法是美元引用。（如果您有未使用美元引用的旧代码，请参阅第 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函数的使用（请参见第 9.4 节）。出于安全考虑，在动态查询中插入之前，包含列或表标识符的表达式应通过quote_ident传递。应将包含应在构造的命令中为文本字面量的表达式的表达式通过quote_literal传递。这些函数采取适当的步骤，分别将输入文本用双引号或单引号引起来，并正确转义任何嵌入的特殊字符。

由于quote_literal被标记为STRICT，因此在使用空参数调用时它始终返回 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的处理效率远低于=，因此除非必须这样做，否则不要这样做。有关 null 和IS DISTINCT的更多信息，请参见第 9.2 节。）

请注意，美元引用仅对引用固定文本有用。尝试将此示例编写为

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

是一个非常糟糕的主意，因为如果newvalue的内容碰巧包含$$，它就会中断。您可能选择的任何其他美元引用定界符也会遇到同样的反对意见。因此，要安全地引用事先不知道的文本，您必须根据需要使用quote_literal、quote_nullable或quote_ident。

也可以使用format函数（请参阅第 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对它们进行引用。它还更有效率。

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

43.5.5. 获取结果状态# ​

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

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

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

GET DIAGNOSTICS integer_var = ROW_COUNT;

表 43.1. 可用的诊断项

名称类型说明
ROW_COUNTbigint最新 命令处理的行数
PG_CONTEXTtext描述当前调用堆栈的文本行（请参阅 第 43.6.9 节）
PG_ROUTINE_OIDoid当前函数的 OID

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

  • SELECT INTO 语句在分配行时将 FOUND 设置为 true，在未返回行时将其设置为 false。

  • PERFORM 语句在产生（并丢弃）一行或多行时将 FOUND 设置为 true，在未产生行时将其设置为 false。

  • UPDATE、INSERT、DELETE 和 MERGE 语句在至少影响一行时将 FOUND 设置为 true，在未影响行时将其设置为 false。

  • FETCH 语句在返回行时将 FOUND 设置为 true，在未返回行时将其设置为 false。

  • MOVE 语句在成功重新定位游标时将 FOUND 设置为 true，否则为 false。

  • FOR 或 FOREACH 语句在迭代一次或多次时将 FOUND 设置为 true，否则为 false。当循环退出时，FOUND 以这种方式设置；在循环执行过程中，FOUND 不会因循环语句而修改，尽管它可能会因循环体中其他语句的执行而改变。

  • RETURN QUERY 和 RETURN QUERY EXECUTE 语句在查询返回至少一行时将 FOUND 设置为 true，在未返回行时将其设置为 false。

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

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

43.5.6. 什么都不做# ​

有时，一个什么都不做的占位符语句是有用的。例如，它可以指示 if/then/else 链的一个分支故意为空。为此，请使用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;

哪一个更好取决于品味。

注意

在 Oracle 的 PL/SQL 中，不允许空语句列表，因此对于这种情况，NULL语句是必需的。相反，PL/pgSQL允许您什么都不写。