Postgresql 中文操作指南
43.5. Basic Statements #
在本节和以下各节中，我们将描述 PL/pgSQL 明确理解的所有语句类型。任何不被识别为这些语句类型之一的语句都被假定为 SQL 命令，并会按 Section 43.5.2中所述发送到主数据库引擎执行。
In this section and the following ones, we describe all the statement types that are explicitly understood by PL/pgSQL. Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute, as described in Section 43.5.2.
43.5.1. Assignment #
向 PL/pgSQL 变量赋值时，应编写为：
An assignment of a value to a PL/pgSQL variable is written as:
variable { := | = } expression;如前所述，此类语句中的表达式通过发送到主数据库引擎的 SQL SELECT 命令进行评估。表达式必须产生一个单一值（如果变量是行或记录变量，则可能是行值）。目标变量可以是简单变量（可以选择用块名称限定）、行或记录目标的字段，或者数组目标的元素或切片。可以使用 Equal (=) 代替兼容 PL/SQL 的 :=。
As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record target, or an element or slice of an array target. Equal (=) can be used instead of PL/SQL-compliant :=.
如果表达式的结果数据类型与变量的数据类型不匹配，则该值将被强制转换，就像一个赋值强制转换（请参见 Section 10.4）。如果不知道涉及的数据类型对的赋值强制转换，则 PL/pgSQL 解释器将尝试按文本形式转换结果值，即应用结果类型输出函数，然后应用变量类型输入函数。请注意，如果输入函数无法接受结果值的字符串形式，可能会导致输入函数生成运行时错误。
If the expression’s result data type doesn’t match the variable’s data type, the value will be coerced as though by an assignment cast (see Section 10.4). If no assignment cast is known for the pair of data types involved, the PL/pgSQL interpreter will attempt to convert the result value textually, that is by applying the result type’s output function followed by the variable type’s input function. Note that this could result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.
示例：
Examples:
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 命令。例如，可以通过编写来创建和填充表
In general, any SQL command that does not return rows can be executed within a PL/pgSQL function just by writing the command. For example, you could create and fill a table by writing
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 循环的数据源。
If the command does return rows (for example SELECT, or INSERT/UPDATE/DELETE with RETURNING), there are two ways to proceed. When the command will return at most one row, or you only care about the first row of output, write the command as usual but add an INTO clause to capture the output, as described in Section 43.5.3. To process all of the output rows, write the command as the data source for a FOR loop, as described in Section 43.6.6.
通常，仅执行静态定义的 SQL 命令是不够的。通常，你希望命令使用不同的数据值，甚至是通过在不同时间使用不同的表名等更基本的方式进行变化。同样，根据实际情况，有两种方法可以继续执行。
Usually it is not sufficient just to execute statically-defined SQL commands. Typically you’ll want a command to use varying data values, or even to vary in more fundamental ways such as by using different table names at different times. Again, there are two ways to proceed depending on the situation.
PL/pgSQL 变量值可以自动插入到可优化 SQL 命令中，这些命令包括 SELECT、INSERT、UPDATE、DELETE、MERGE，以及包含其中之一的某些实用程序命令，例如 EXPLAIN 和 CREATE TABLE … AS SELECT。在这些命令中，出现在命令文本中的任何 PL/pgSQL 变量名称都在运行时被查询参数所替代，然后变量的当前值作为参数值提供。这与前面针对表达式所述的处理过程完全相同；有关详细信息，请参见 Section 43.11.1。
PL/pgSQL variable values can be automatically inserted into optimizable SQL commands, which are SELECT, INSERT, UPDATE, DELETE, MERGE, and certain utility commands that incorporate one of these, such as EXPLAIN and CREATE TABLE … AS SELECT. In these commands, any PL/pgSQL variable name appearing in the command text is replaced by a query parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see Section 43.11.1.
通过这种方式执行优化 SQL 命令时，PL/pgSQL 可能会缓存并重复使用命令的执行计划，如 Section 43.11.2 中所述。
When executing an optimizable SQL command in this way, PL/pgSQL may cache and re-use the execution plan for the command, as discussed in Section 43.11.2.
不可优化的 SQL 命令（也称为实用程序命令）无法接受查询参数。因此，PL/pgSQL 变量的自动替换不适用于此类命令。要将非常量文本包含在从 PL/pgSQL 执行的实用程序命令中，您必须将实用程序命令构建为字符串，然后 EXECUTE 它，如 Section 43.5.4 中所述。
Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution of PL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed from PL/pgSQL, you must build the utility command as a string and then EXECUTE it, as discussed in Section 43.5.4.
如果您希望除了提供数据值外还以其他方式修改命令，例如更改表名称，也务必使用 EXECUTE。
EXECUTE must also be used if you want to modify the command in some other way than supplying a data value, for example by changing a table name.
有时评估表达式或 SELECT 查询却丢弃结果是有用的，例如在调用具有副作用但无有用结果值时。要在 PL/pgSQL 中执行此操作，请使用 PERFORM 语句：
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:
PERFORM query;这将执行 query 并丢弃结果。编写 query 的方式与编写 SQL SELECT 的方式相同，但是将初始关键字 SELECT 替换为 PERFORM。对于 WITH 查询，请使用 PERFORM，然后将查询括在圆括号中。（在这种情况下，查询只能返回一行。）PL/pgSQL 变量将按上述方式替换到查询中，并且将计划按相同的方式缓存。此外，如果查询产生了至少一行，则将特殊变量 FOUND 设置为真，如果它没有产生行，则设置成假（参见 Section 43.5.5）。
This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM. For WITH queries, use PERFORM and then place the query in parentheses. (In this case, the query can only return one row.) PL/pgSQL variables will be substituted into the query just as described above, and the plan is cached in the same way. Also, the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 43.5.5).
Note
人们可能会希望直接编写 SELECT 来实现此结果，但目前唯一可接受的方法是 PERFORM。可以返回行的 SQL 命令（例如 SELECT），除非它具有 INTO 子句（如下一节所述），否则会被拒绝为错误。
One might expect that writing SELECT directly would accomplish this result, but at present the only accepted way to do it is PERFORM. An SQL command that can return rows, such as SELECT, will be rejected as an error unless it has an INTO clause as discussed in the next section.
示例：
An example:
PERFORM create_mv('cs_session_page_requests_mv', my_query);43.5.3. Executing a Command with a Single-Row Result #
生成单行的（可能是多列的）SQL 命令的结果可以分配给记录变量、行类型变量或标量变量列表。这是通过编写基本 SQL 命令并添加 INTO 子句来实现的。例如，
The result of an SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. For example,
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 外部编写的方式相同。
where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. PL/pgSQL variables will be substituted into the rest of the command (that is, everything but the INTO clause) just as described above, and the plan is cached in the same way. This works for SELECT, INSERT/UPDATE/DELETE with RETURNING, and certain utility commands that return row sets, such as EXPLAIN. Except for the INTO clause, the SQL command is the same as it would be written outside PL/pgSQL.
Tip
请注意，此 SELECT 和 INTO 的解释与 PostgreSQL 的常规 SELECT INTO 命令完全不同，其中 INTO 目标是新创建的表。如果您想在 PL/pgSQL 函数内从 SELECT 结果创建表，请使用 CREATE TABLE … AS SELECT 语法。
Note that this interpretation of SELECT with INTO is quite different from PostgreSQL’s regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE … AS SELECT.
如果行变量或变量列表用作目标，则命令的结果列必须与目标的数量和数据类型完全匹配，否则会导致运行时错误。当记录变量是目标时，它会自动配置到命令结果列的行类型。
If a row variable or a variable list is used as target, the command’s result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the command’s result columns.
INTO 子句几乎可以在 SQL 命令中的任何位置出现。通常，它在 SELECT 命令的 select_expressions 列表前或后书写一次，或在其他命令类型命令的末尾书写一次。务必遵循此规则，以免未来的版本中 PL/pgSQL 解析器更加严格。
The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions.
如果在 INTO 子句中未指定 STRICT，则 target 将设置成该命令返回的第一行，或者如果命令没有返回行，则将其设置成 null。（请注意，“第一行” 的定义不明确，除非您使用了 ORDER BY。）第一行之后的所有结果行都会被丢弃。您可以检查特殊 FOUND 变量（参见 Section 43.5.5）来确定是否返回了一行：
If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the command, or to nulls if the command returned no rows. (Note that “the first row” is not well-defined unless you’ve used ORDER BY.) Any result rows after the first row are discarded. You can check the special FOUND variable (see Section 43.5.5) to determine whether a row was returned:
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（多行）。如果您希望捕捉该错误， можете使用一个异常块，例如：
If the STRICT option is specified, the command must return exactly one row or a run-time error will be reported, either NO_DATA_FOUND (no rows) or TOO_MANY_ROWS (more than one row). You can use an exception block if you wish to catch the error, for example:
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。
Successful execution of a command with STRICT always sets FOUND to true.
对于带有 RETURNING 的 INSERT/UPDATE/DELETE，即使未指定 STRICT，PL/pgSQL 也会对多行返回报告错误。这是因为没有诸如 ORDER BY 等选项来确定应该返回哪一行。
For INSERT/UPDATE/DELETE with RETURNING, PL/pgSQL reports an error for more than one returned row, even when STRICT is not specified. This is because there is no option such as ORDER BY with which to determine which affected row should be returned.
如果函数启用了 print_strict_params，则当由于不满足 STRICT 的要求而抛出错误时，错误消息的 DETAIL 部分将包含有关传递给命令的参数的信息。您可以通过设置 plpgsql.print_strict_params 更改所有函数的 print_strict_params 设置，但仅会影响后续函数编译。您还可以使用编译器选项逐个函数启用它，例如：
If print_strict_params is enabled for the function, then when an error is thrown because the requirements of STRICT are not met, the DETAIL part of the error message will include information about the parameters passed to the command. You can change the print_strict_params setting for all functions by setting plpgsql.print_strict_params, though only subsequent function compilations will be affected. You can also enable it on a per-function basis by using a compiler option, for example:
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;在失败的情况下，此函数可能会生成如下错误消息：
On failure, this function might produce an error message such as
ERROR: query returned no rows
DETAIL: parameters: $1 = 'nosuchuser'
CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statementNote
STRICT 选项匹配 Oracle PL/SQL 的 SELECT INTO 和相关语句的行为。
The STRICT option matches the behavior of Oracle PL/SQL’s SELECT INTO and related statements.
43.5.4. Executing Dynamic Commands #
通常，您希望在 PL/pgSQL 函数中生成动态命令，即在每次执行时都会涉及不同的表或不同数据类型的命令。PL/pgSQL 正常尝试缓存命令的计划（如 Section 43.11.2 中所述）在这样的场景中不起作用。要处理此类问题，提供了 EXECUTE 语句：
Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL’s normal attempts to cache plans for commands (as discussed in Section 43.11.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];其中 command-string 是生成包含要执行的命令的字符串（类型 text）的表达式。可选 target 是记录变量、行变量或按逗号分隔的简单变量和记录/行字段列表，将命令的结果存储到其中。可选 USING 表达式提供要插入到命令中的值。
where command-string is an expression yielding a string (of type text) containing the command to be executed. The optional target is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored. The optional USING expressions supply values to be inserted into the command.
PL/pgSQL 变量未在计算的命令字符串上进行任何替换。任何必需的变量值必须在构造命令字符串时插入其中；或者您可以按如下所述使用参数。
No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.
另外，通过 EXECUTE 执行的命令没有计划缓存。相反，每次运行语句时都会始终规划命令。因此，可以在函数内动态创建命令字符串，以便对不同的表和列执行操作。
Also, there is no plan caching for commands executed via EXECUTE. Instead, the command is always planned each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns.
INTO 子句指定应将返回行的 SQL 命令的结果分配到何处。如果提供了行变量或变量列表，则它必须与命令结果的结构完全匹配；如果提供了记录变量，它会自动配置自己以匹配结果结构。如果返回了多行，则只将第一行分配给 INTO 变量。如果没有返回任何行，则向 INTO 变量分配 NULL。如果没有指定 INTO 子句，则会丢弃命令结果。
The INTO clause specifies where the results of an SQL command returning rows should be assigned. If a row variable or variable list is provided, it must exactly match the structure of the command’s results; if a record variable is provided, it will configure itself to match the result structure automatically. If multiple rows are returned, only the first will be assigned to the INTO variable(s). If no rows are returned, NULL is assigned to the INTO variable(s). If no INTO clause is specified, the command results are discarded.
如果给定了 STRICT 选项，则会报告一个错误，除非命令准确地生成一行。
If the STRICT option is given, an error is reported unless the command produces exactly one row.
命令字符串可以使用参数值，在命令中引用为 $1、$2 等。这些符号引用 USING 子句中提供的的值。这种方法通常优于将数据值作为文本插入到命令字符串中：它避免了将值转换为文本的运行时开销，并且非常不容易出现 SQL 注入攻击，因为不需要引用或转义。一个例子是：
The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;请注意，参数符号只能用于数据值，如果您想使用动态确定的表或列名，则必须将它们作为文本插入到命令字符串中。例如，如果需要根据动态选择的表来执行上述查询，则可以执行以下操作：
Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;一种更简洁的方法是使用 format() 的 %I 规范，使用自动引用来插入表或列名：
A cleaner approach is to use format()'s %I specification to insert table or column names with automatic quoting:
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND inserted <= $2', tabname)
INTO c
USING checked_user, checked_date;（此示例依赖于 SQL 规则，即由换行符分隔的字符串文字会隐式连接。）
(This example relies on the SQL rule that string literals separated by a newline are implicitly concatenated.)
对参数符号的另一个限制是，它们只适用于可优化 SQL 命令（SELECT、INSERT、UPDATE、DELETE、MERGE，以及包含其中一个命令的某些命令）。在其他语句类型中（通常称为实用程序语句），您必须以文本方式插入值，即使它们只是数据值。
Another restriction on parameter symbols is that they only work in optimizable SQL commands (SELECT, INSERT, UPDATE, DELETE, MERGE, and certain commands containing one of these). In other statement types (generically called utility statements), you must insert values textually even if they are just data values.
EXECUTE，带有简单的常量命令字符串和一些 USING 参数，如上面第一个示例中，在功能上等同于直接在 PL/pgSQL 中编写命令并允许 PL/pgSQL 变量替换自动发生。重要的区别在于 EXECUTE 将在每次执行时重新规划命令，生成一个特定于当前参数值的计划；而 PL/pgSQL 可能创建通用计划并将其缓存以供重用。在最佳计划严重依赖于参数值的情况下，使用 EXECUTE 可以帮助确认未选择通用计划。
An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas PL/pgSQL may otherwise create a generic plan and cache it for re-use. In situations where the best plan depends strongly on the parameter values, it can be helpful to use EXECUTE to positively ensure that a generic plan is not selected.
SELECT INTO 目前不受 EXECUTE 支持；相反，执行一个简单的 SELECT 命令，并指定 INTO 作为 EXECUTE 的一部分。
SELECT INTO is not currently supported within EXECUTE; instead, execute a plain SELECT command and specify INTO as part of the EXECUTE itself.
Note
PL/pgSQL EXECUTE 语句与 PostgreSQL 服务器支持的 EXECUTE SQL 语句无关。服务器的 EXECUTE 语句无法直接在 PL/pgSQL 函数中使用（并且不需要）。
The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server’s EXECUTE statement cannot be used directly within PL/pgSQL functions (and is not needed).
Example 43.1. Quoting Values in Dynamic Queries
在使用动态命令时，您经常必须处理单引号转义。在函数主体中引用固定文本的建议方法是美元引用。（如果您有未使用美元引用的旧代码，请参阅 Section 43.12.1 中的概览，这可以在将上述代码转换为更合理的方案时节省您的精力。）
When working with dynamic commands you will often have to handle escaping of single quotes. The recommended method for quoting fixed text in your function body is dollar quoting. (If you have legacy code that does not use dollar quoting, please refer to the overview in Section 43.12.1, which can save you some effort when translating said code to a more reasonable scheme.)
动态值需要谨慎处理，因为它们可能包含引号字符。使用 format() 的示例（假设您正在使用美元引用函数正文，因此无需重复引号）：
Dynamic values require careful handling since they might contain quote characters. An example using format() (this assumes that you are dollar quoting the function body so quote marks need not be doubled):
EXECUTE format('UPDATE tbl SET %I = $1 '
'WHERE key = $2', colname) USING newvalue, keyvalue;也可以直接调用引用函数：
It is also possible to call the quoting functions directly:
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 传递在构造的命令中应包含字面字符串的值的表达式。这些函数分别采取适当的步骤来返回用双引号或单引号引起来输入文本，并正确转义所有嵌入的特殊字符。
This example demonstrates the use of the quote_ident and quote_literal functions (see Section 9.4). For safety, expressions containing column or table identifiers should be passed through quote_ident before insertion in a dynamic query. Expressions containing values that should be literal strings in the constructed command should be passed through quote_literal. These functions take the appropriate steps to return the input text enclosed in double or single quotes respectively, with any embedded special characters properly escaped.
因为 quote_literal 被标记为 STRICT，所以当使用 null 参数调用时它将始终返回 null。在上面的示例中，如果 newvalue 或 keyvalue 为 null，则整个动态查询字符串将变为 null，从而导致 EXECUTE 出错。您可以通过使用 quote_nullable 函数来避免此问题，该函数与 quote_literal 的工作方式相同，只是在使用 null 参数调用时它返回字符串 NULL。例如，
Because quote_literal is labeled STRICT, it will always return null when called with a null argument. In the above example, if newvalue or keyvalue were null, the entire dynamic query string would become null, leading to an error from EXECUTE. You can avoid this problem by using the quote_nullable function, which works the same as quote_literal except that when called with a null argument it returns the string NULL. For example,
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);如果您处理的值可能为 null，则通常应该使用 quote_nullable 替换 quote_literal。
If you are dealing with values that might be null, you should usually use quote_nullable in place of quote_literal.
与往常一样，必须小心确保查询中的 null 值不会产生意外结果。例如 WHERE 子句
As always, care must be taken to ensure that null values in a query do not deliver unintended results. For example the WHERE clause
'WHERE key = ' || quote_nullable(keyvalue)如果 keyvalue 为 null，则永远不会成功，因为使用等号运算符 = 和 null 操作数，其结果永远为 null。如果您希望 null 像普通键值一样工作，那么您需要将上面重写为：
will never succeed if keyvalue is null, because the result of using the equality operator = with a null operand is always null. If you wish null to work like an ordinary key value, you would need to rewrite the above as
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)（目前，与 = 相比，IS NOT DISTINCT FROM 的处理效率低得多，因此除非必要，否则请不要这样做。有关空值和 IS DISTINCT 的详细信息，请参见 Section 9.2。）
(At present, IS NOT DISTINCT FROM is handled much less efficiently than =, so don’t do this unless you must. See Section 9.2 for more information on nulls and IS DISTINCT.)
请注意，美元引用仅可用于引用固定文本。尝试将此示例写成如下形式是一个非常糟糕的想法：
Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to write this example as:
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);因为如果碰巧 newvalue 的内容包含 $$，它将会中断。您可能会选择的任何其他美元引用定界符都会遇到相同的反对意见。因此，为了安全地引用事先未知的文本，您 must 会根据具体情况使用 quote_literal、quote_nullable 或 quote_ident。
because it would break if the contents of newvalue happened to contain $$. The same objection would apply to any other dollar-quoting delimiter you might pick. So, to safely quote text that is not known in advance, you must use quote_literal, quote_nullable, or quote_ident, as appropriate.
还可以使用 format 函数（请参见 Section 9.4.1）安全地构造动态 SQL 语句。例如：
Dynamic SQL statements can also be safely constructed using the format function (see Section 9.4.1). For example:
EXECUTE format('UPDATE tbl SET %I = %L '
'WHERE key = %L', colname, newvalue, keyvalue);%I 等效于 quote_ident，%L 等效于 quote_nullable。format 函数可与 USING 子句结合使用：
%I is equivalent to quote_ident, and %L is equivalent to quote_nullable. The format function can be used in conjunction with the USING clause:
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;此种形式更好，因为变量以其原生数据类型格式进行处理，而不是无条件地将它们转换为文本并通过 %L 对它们进行引用。它也更有效。
This form is better because the variables are handled in their native data type format, rather than unconditionally converting them to text and quoting them via %L. It is also more efficient.
可以在 Example 43.10 中看到一个更大的动态命令示例和 EXECUTE，它构建并执行一个 CREATE FUNCTION 命令来定义一个新函数。
A much larger example of a dynamic command and EXECUTE can be seen in Example 43.10, which builds and executes a CREATE FUNCTION command to define a new function.
43.5.5. Obtaining the Result Status #
有几种方法可以确定命令的效果。第一种方法是使用 GET DIAGNOSTICS 命令，其形式为：
There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:
GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];此命令允许检索系统状态指示符。CURRENT 是一个噪音词（但在 Section 43.6.8.1 中也参见 GET STACKED DIAGNOSTICS）。每个 item 都是一个关键词，用于识别要分配给指定 variable（其应为可以接收它的正确数据类型）的状态值。 Table 43.1 中显示了当前可用的状态项。可以使用冒号等于 (:=) 来代替 SQL 标准 = 令牌。一个示例：
This command allows retrieval of system status indicators. CURRENT is a noise word (but see also GET STACKED DIAGNOSTICS in Section 43.6.8.1). Each item is a key word identifying a status value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are shown in Table 43.1. Colon-equal (:=) can be used instead of the SQL-standard = token. An example:
GET DIAGNOSTICS integer_var = ROW_COUNT;Table 43.1. Available Diagnostics Items
Name |
Type |
Description |
ROW_COUNT |
bigint |
the number of rows processed by the most recent SQL command |
PG_CONTEXT |
text |
line(s) of text describing the current call stack (see Section 43.6.9) |
PG_ROUTINE_OID |
oid |
OID of the current function |
确定命令效果的第二种方法是检查一个名为 FOUND 的特殊变量，其类型为 boolean。在每个 PL/pgSQL 函数调用中，FOUND 起始值为 false。它由以下类型的每个语句设置：
The second method to determine the effects of a command is to check the special variable named FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:
其他 PL/pgSQL 语句不会改变 FOUND 的状态。特别要注意，EXECUTE 更改 GET DIAGNOSTICS 的输出，但不会更改 FOUND。
Other PL/pgSQL statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.
FOUND 是每个 PL/pgSQL 函数中的局部变量；对其进行的任何更改只会影响当前函数。
FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.
43.5.6. Doing Nothing At All #
有时，一个什么都不做的占位语句很有用。例如，它可以指示一个 if/then/else chain 的一条分支是故意留空的。为此，请使用 NULL 语句：
Sometimes a placeholder statement that does nothing is useful. For example, it can indicate that one arm of an if/then/else chain is deliberately empty. For this purpose, use the NULL statement:
NULL;例如，以下两个代码段等效：
For example, the following two fragments of code are equivalent:
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;哪一个更好取决于个人喜好。
Which is preferable is a matter of taste.