Postgresql 中文操作指南

43.4. Expressions #

PL/pgSQL 语句中使用的所有表达式都会利用服务器的主 SQL 执行程序进行处理。例如，当你编写类似如下所示的 PL/pgSQL 语句时

All expressions used in PL/pgSQL statements are processed using the server’s main SQL executor. For example, when you write a PL/pgSQL statement like

IF expression THEN ...

PL/pgSQL 会通过向主 SQL 引擎发送类似如下所示的查询来评估该表达式

PL/pgSQL will evaluate the expression by feeding a query like

SELECT expression

到主 SQL 引擎。在形成 SELECT 命令时，PL/pgSQL 变量名的任何出现都由查询参数替换，如 Section 43.11.1 中详述。这允许 SELECT 的查询计划只准备一次，然后在变量具有不同值的情况下针对后续评估重用。因此，第一次使用表达式时实际发生的是一个 PREPARE 命令。例如，如果我们声明了两个整型变量 x 和 y，并且我们写道：

to the main SQL engine. While forming the SELECT command, any occurrences of PL/pgSQL variable names are replaced by query parameters, as discussed in detail in Section 43.11.1. This allows the query plan for the SELECT to be prepared just once and then reused for subsequent evaluations with different values of the variables. Thus, what really happens on first use of an expression is essentially a PREPARE command. For example, if we have declared two integer variables x and y, and we write

IF x < y THEN ...

那么幕后发生的情况等同于

what happens behind the scenes is equivalent to

PREPARE statement_name(integer, integer) AS SELECT $1 < $2;

那么这个预处理语句将是 EXECUTE_d for each execution of the _IF 语句，其中当前值以参数值的形式提供给 PL/pgSQL 变量。通常这些细节对 PL/pgSQL 用户来说并不重要，但在尝试诊断问题时了解它们非常有用。更多信息请参阅 Section 43.11.2。

and then this prepared statement is EXECUTE_d for each execution of the _IF statement, with the current values of the PL/pgSQL variables supplied as parameter values. Normally these details are not important to a PL/pgSQL user, but they are useful to know when trying to diagnose a problem. More information appears in Section 43.11.2.

由于 expression 已转换为 SELECT 命令，因此它可以包含普通 SELECT 所包含的相同子句，但它不能包含顶级 UNION、INTERSECT 或 EXCEPT 子句。因此，例如，我们可以使用以下命令来测试表是否不为空

Since an expression is converted to a SELECT command, it can contain the same clauses that an ordinary SELECT would, except that it cannot include a top-level UNION, INTERSECT, or EXCEPT clause. Thus for example one could test whether a table is non-empty with

IF count(*) > 0 FROM my_table THEN ...

因为 IF 和 THEN 之间的 expression 被解析时就如同它就是 SELECT count(*) > 0 FROM my_table 一样。SELECT 必须只产生一列数据，并且不能产生多于一行数据。（如果它不产生任何行数据，则结果将被视为 NULL。）

since the expression between IF and THEN is parsed as though it were SELECT count(*) > 0 FROM my_table. The SELECT must produce a single column, and not more than one row. (If it produces no rows, the result is taken as NULL.)