43.5. 基本声明

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

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

43 .5.2. 无结果执行命令

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

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

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

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

PERFORM query;

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

Note

可能有人期望直接编写SELECT可以完成此结果,但是目前唯一接受的方法是PERFORM。可以返回行的 SQL 命令(例如SELECT)将被拒绝为错误,除非它具有INTO子句,这将在下一节中讨论。

An example:

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

Tip

请注意,使用INTOSELECT的解释与 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。

对于带有RETURNINGINSERT/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和相关语句的行为匹配。

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

43 .5.4. 执行动态命令

通常,您可能希望在 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;

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

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

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

Note

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

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

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

因为quote_literal被标记为STRICT,所以在使用 null 参数调用时,它将始终返回 null。在上面的示例中,如果newvaluekeyvalue为空,则整个动态查询字符串将为空,从而导致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_literalquote_nullablequote_ident

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

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

%I等效于quote_ident,而%L等效于quote_nullableformat函数可以与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. 获取结果状态

有几种方法可以确定命令的效果。第一种方法是使用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;

表 43.1. 可用的诊断项目

NameTypeDescription
ROW_COUNTbigint最近的 SQL 命令处理的行数
RESULT_OIDoid最近的 SQL 命令插入的最后一行的 OID(仅在INSERT命令插入到具有 OID 的表中后才有用)
PG_CONTEXTtext描述当前调用堆栈的文本行(请参见Section 43.6.9)

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

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

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

  • UPDATEINSERTDELETE语句设置FOUND,如果至少影响一行,则为 true;如果不影响任何行,则为 false。

  • 如果FETCH语句返回一行,则将FOUND设置为 true;如果不返回任何行,则将其设置为 false。

  • MOVE语句将FOUND设置为 true(如果它成功地重新定位了光标),否则设置为 false。

  • 如果FORFOREACH语句重复一次或多次,则将FOUND设置为 true,否则为 false。循环退出时以这种方式设置FOUND;在循环执行中,FOUND不会被循环语句修改,尽管在循环体内执行其他语句可能会更改它。

  • RETURN QUERYRETURN 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;

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

Note

在 Oracle 的 PL/SQL 中,不允许使用空语句列表,因此对于此类情况,“ *”是NULL语句。 PL/pgSQL 允许您只写任何内容。