42.5. 基本声明

• 42.5.1. Assignment

• 42 .5.2. 无结果执行命令

• 42 .5.3. 用单行结果执行查询

• 42 .5.4. 执行动态命令

• 42 .5.5. 获取结果状态

• 42 .5.6. 什么都不做

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

42.5.1. Assignment

将值分配给 PL/pgSQL 变量的形式为：

variable { := | = } expression;

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

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

Examples:

tax := subtotal * 0.06;
my_record.user_id := 20;

42 .5.2. 无结果执行命令

对于任何不返回行的 SQL 命令(例如不带RETURNING子句的INSERT)，只需编写命令即可在 PL/pgSQL 函数中执行该命令。

命令文本中出现的任何 PL/pgSQL 变量名称都被视为参数，然后在运行时将变量的当前值作为参数值提供。这与前面描述的表达式完全相同。有关详细信息，请参见Section 42.10.1。

当以这种方式执行 SQL 命令时，PL/pgSQL 可以缓存并重新使用该命令的执行计划，如Section 42.10.2中所述。

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

PERFORM query;

这将执行* query 并丢弃结果。编写 query *的方式与编写 SQL SELECT命令的方式相同，只是将初始关键字SELECT替换为PERFORM。对于WITH查询，请使用PERFORM，然后将查询放在括号中。 (在这种情况下，查询只能返回一行.)PL/pgSQL 变量将替换为查询，就像不返回任何结果的命令一样，并且以相同的方式缓存计划。另外，如果查询产生至少一行，则将特殊变量FOUND设置为 true；如果不产生任何行，则将其设置为 false(请参见Section 42.5.5)。

An example:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

42 .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 变量将被替换为查询的其余部分，并且计划被缓存，就像上面针对不返回行的命令所述。这适用于SELECT，INSERT/UPDATE/DELETE和RETURNING以及返回行集结果的 Util 命令(例如EXPLAIN)。除了INTO子句外，SQL 命令与在 PL/pgSQL 外部编写的命令相同。

如果将行或变量列表用作目标，则查询的结果列必须与数字和数据类型的目标结构完全匹配，否则会发生运行时错误。当记录变量为目标时，它会自动将其自身配置为查询结果列的行类型。

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

如果在INTO子句中未指定STRICT，则* target *将设置为查询返回的第一行，如果查询未返回任何行，则将其设置为 null。 (请注意，除非已使用ORDER BY，否则“第一行”的定义并不明确.)第一行之后的所有结果行都将被丢弃。您可以检查特殊的FOUND变量(请参见Section 42.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

要处理需要处理来自 SQL 查询的多个结果行的情况，请参见Section 42.6.4。

42 .5.4. 执行动态命令

通常，您可能希望在 PL/pgSQL 函数内部生成动态命令，即每次执行时将涉及不同表或不同数据类型的命令。在这种情况下，PL/pgSQL 正常尝试缓存命令计划的尝试(如Section 42.10.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;

参数符号的另一个限制是它们只能在SELECT，INSERT，UPDATE和DELETE命令中使用。在其他语句类型(通常称为 Util 语句)中，即使它们只是数据值，也必须以文本形式插入值。

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

EXECUTE目前不支持SELECT INTO；而是执行简单的SELECT命令，并将INTO指定为EXECUTE本身的一部分。

实施例 42.1. 动态查询中的报价值

使用动态命令时，通常必须处理转义单引号。在功能体内引用固定文本的推荐方法是美元引用。 (如果您有不使用美元引号的旧代码，请参阅Section 42.11.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传递。包含在构造的命令中应为 Literals 字符串的值的表达式应通过quote_literal传递。这些函数将采取适当的步骤来返回分别用双引号或单引号引起来的 Importing 文本，并正确地转义任何嵌入的特殊字符。

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

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

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

与往常一样，必须注意确保查询中的空值不会传递意外的结果。例如WHERE子句

'WHERE key = ' || quote_nullable(keyvalue)

如果keyvalue为空，将永远不会成功，因为使用等于运算符=和空操作数的结果始终为空。如果希望 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的内容碰巧包含$$，它将损坏。相同的异议将适用于您可能选择的任何其他美元报价分隔符。因此，为了安全地引用事先未知的文本，您必须*适当地使用quote_literal，quote_nullable或quote_ident。

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

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 42.10中看到动态命令和EXECUTE的更大示例，该示例生成并执行CREATE FUNCTION命令以定义新功能。

42 .5.5. 获取结果状态

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

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

该命令允许检索系统状态指示器。 CURRENT是一个干扰词(但也请参见Section 42.6.6.1中的GET STACKED DIAGNOSTICS)。每个* item 是一个关键字，用于标识要分配给指定的 variable *的状态值(应该具有正确的数据类型以接收它)。当前可用的状态项显示在Table 42.1中。可以使用冒号等号(:=)代替 SQL 标准的=令牌。一个例子：

GET DIAGNOSTICS integer_var = ROW_COUNT;

表 42.1. 可用的诊断项目

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

• 如果分配了行，则SELECT INTO语句将FOUND设置为 true；如果不返回任何行，则设置为 false。

• 如果PERFORM语句产生(并丢弃)一行或多行，则将FOUND设置为 true；如果不产生任何行，则将设置为FOUND。

• UPDATE，INSERT和DELETE语句设置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 函数中的局部变量；对其所做的任何更改仅影响当前功能。

42 .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;

关于味道，哪个是优选的。