Postgresql 中文操作指南

43.3. Declarations #

块中使用的所有变量都必须在块的声明部分中声明。（唯一的例外是迭代在整数值范围内的一个_FOR_循环的循环变量会自动声明为整数变量，同样地，迭代游标结果的一个_FOR_循环的循环变量会自动声明为记录变量。）

All variables used in a block must be declared in the declarations section of the block. (The only exceptions are that the loop variable of a FOR loop iterating over a range of integer values is automatically declared as an integer variable, and likewise the loop variable of a FOR loop iterating over a cursor’s result is automatically declared as a record variable.)

PL/pgSQL 变量可以有任意 SQL 数据类型，例如_integer_、varchar_和_char。

PL/pgSQL variables can have any SQL data type, such as integer, varchar, and char.

以下是一些变量声明的示例：

Here are some examples of variable declarations:

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

变量声明的一般语法是：

The general syntax of a variable declaration is:

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

如果给出了 DEFAULT 子句，则指定在进入块时分配给变量的初始值。如果未给定 DEFAULT 子句，则变量将初始化为 SQL null 值。 CONSTANT 选项防止在初始化后将变量赋值，以便其值在块的持续时间内保持不变。 COLLATE 选项指定用于变量的校对规则（请参见 Section 43.3.6 ）。如果指定了 NOT NULL ，那么将 null 值赋值的结果将导致运行时错误。声明为 NOT NULL 的所有变量必须指定非 null 默认值。可以使用等号 (=) 代替 PL/SQL 兼容 := 。

The DEFAULT clause, if given, specifies the initial value assigned to the variable when the block is entered. If the DEFAULT clause is not given then the variable is initialized to the SQL null value. The CONSTANT option prevents the variable from being assigned to after initialization, so that its value will remain constant for the duration of the block. The COLLATE option specifies a collation to use for the variable (see Section 43.3.6). If NOT NULL is specified, an assignment of a null value results in a run-time error. All variables declared as NOT NULL must have a nonnull default value specified. Equal (=) can be used instead of PL/SQL-compliant :=.

变量的默认值在每次输入块时都会计算并分配给该变量（不仅仅是每次函数调用）。因此，例如，将 now() 分配给类型为 timestamp 的变量，会导致变量具有当前函数调用的时间，而不是函数预编译时的时间。

A variable’s default value is evaluated and assigned to the variable each time the block is entered (not just once per function call). So, for example, assigning now() to a variable of type timestamp causes the variable to have the time of the current function call, not the time when the function was precompiled.

示例：

Examples:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
transaction_time CONSTANT timestamp with time zone := now();

一旦声明，就可以在同一块中后面的初始化表达式中使用变量的值，例如：

Once declared, a variable’s value can be used in later initialization expressions in the same block, for example:

DECLARE
  x integer := 1;
  y integer := x + 1;

43.3.1. Declaring Function Parameters #

传递给函数的参数以标识符 $1 、 $2 等等命名。对于 $_n_ 参数名称，可以声明别名以提高可读性。然后可以使用别名或数字标识符来引用参数值。

Parameters passed to functions are named with the identifiers $1, $2, etc. Optionally, aliases can be declared for $_n_ parameter names for increased readability. Either the alias or the numeric identifier can then be used to refer to the parameter value.

创建别名有两种方法。首选的方法是在 CREATE FUNCTION 命令中为参数命名，例如：

There are two ways to create an alias. The preferred way is to give a name to the parameter in the CREATE FUNCTION command, for example:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

另一种方法是使用声明语法显式声明别名

The other way is to explicitly declare an alias, using the declaration syntax

name ALIAS FOR $n;

这种风格的示例如下：

The same example in this style looks like:

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Note

这两个示例并非完全等效。在第一种情况下，subtotal_可以被引用为_sales_tax.subtotal，但在第二种情况下不能。(如果我们给内部块附加标签，则_subtotal_可以用该标签限定。)

These two examples are not perfectly equivalent. In the first case, subtotal could be referenced as sales_tax.subtotal, but in the second case it could not. (Had we attached a label to the inner block, subtotal could be qualified with that label, instead.)

更多一些示例：

Some more examples:

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

当使用输出参数声明 PL/pgSQL 函数时，输出参数将以 $_n_ 名称和可选别名指定，方式与普通输入参数完全相同。输出参数实际上是一个以 NULL 开始的变量；它应在函数执行期间被赋值。参数的最终值即是返回的值。例如，也可以通过以下方式完成销售税示例：

When a PL/pgSQL function is declared with output parameters, the output parameters are given $_n_ names and optional aliases in just the same way as the normal input parameters. An output parameter is effectively a variable that starts out NULL; it should be assigned to during the execution of the function. The final value of the parameter is what is returned. For instance, the sales-tax example could also be done this way:

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

请注意，我们省略了 RETURNS real — 我们本可以包含它，但它是多余的。

Notice that we omitted RETURNS real — we could have included it, but it would be redundant.

要调用带有 OUT 参数的函数，请在函数调用中省略输出参数：

To call a function with OUT parameters, omit the output parameter(s) in the function call:

SELECT sales_tax(100.00);

当返回多个值时，输出参数最有用途。一个简单的例子是：

Output parameters are most useful when returning multiple values. A trivial example is:

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM sum_n_product(2, 4);
 sum | prod
-----+------
   6 |    8

如 Section 38.5.4 所述，这实际上为函数结果创建了一个匿名的记录类型。如果给出了 RETURNS 子句，则它必须为 RETURNS record 。

As discussed in Section 38.5.4, this effectively creates an anonymous record type for the function’s results. If a RETURNS clause is given, it must say RETURNS record.

这也适用于过程，例如：

This also works with procedures, for example:

CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

在调用过程中，必须指定所有参数。对于输出参数，从纯 SQL 调用过程时，可以指定 NULL：

In a call to a procedure, all the parameters must be specified. For output parameters, NULL may be specified when calling the procedure from plain SQL:

CALL sum_n_product(2, 4, NULL, NULL);
 sum | prod
-----+------
   6 |    8

但是，当从 PL/pgSQL 调用过程时，应为任何输出参数编写一个变量；该变量将接收调用的结果。有关详细信息，请参见 Section 43.6.3 。

However, when calling a procedure from PL/pgSQL, you should instead write a variable for any output parameter; the variable will receive the result of the call. See Section 43.6.3 for details.

声明 PL/pgSQL 函数的另一种方法是 RETURNS TABLE，例如：

Another way to declare a PL/pgSQL function is with RETURNS TABLE, for example:

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

这与声明一个或多个 OUT 参数并指定 RETURNS SETOF _sometype_ 完全相同。

This is exactly equivalent to declaring one or more OUT parameters and specifying RETURNS SETOF _sometype_.

如果 PL/pgSQL 函数的返回类型声明为多态类型（请参见 Section 38.2.5 ），则创建特殊参数 $0 。它的数据类型为函数的实际返回类型，根据实际输入类型推断得出。这样，函数就可以访问其实际返回类型，如 Section 43.3.3 中所示。 $0 初始化为 null，并且函数可以对其进行修改，因此可以将其用于保存返回的值（如果需要的话），但这不是必需的。 $0 也可以指定一个别名。例如，此函数适用于具有 + 运算符的任何数据类型：

When the return type of a PL/pgSQL function is declared as a polymorphic type (see Section 38.2.5), a special parameter $0 is created. Its data type is the actual return type of the function, as deduced from the actual input types. This allows the function to access its actual return type as shown in Section 43.3.3. $0 is initialized to null and can be modified by the function, so it can be used to hold the return value if desired, though that is not required. $0 can also be given an alias. For example, this function works on any data type that has a + operator:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

可以通过将一个或多个输出参数声明为多态类型来获得相同的效果。在这种情况下，不使用 special $0 参数；输出参数本身具有相同目的。例如：

The same effect can be obtained by declaring one or more output parameters as polymorphic types. In this case the special $0 parameter is not used; the output parameters themselves serve the same purpose. For example:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

在实践中，使用 anycompatible 系列类型来声明多态函数可能会更有用，以便自动将输入参数提升为一个公共类型。例如：

In practice it might be more useful to declare a polymorphic function using the anycompatible family of types, so that automatic promotion of the input arguments to a common type will occur. For example:

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURNS anycompatible AS $$
BEGIN
    RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

借助此示例，如下调用：

With this example, a call such as

SELECT add_three_values(1, 2, 4.7);

将起作用，自动将整型输入提升为数字类型。使用 anyelement 的函数将要求您手动将三个输入强制转换为相同类型。

will work, automatically promoting the integer inputs to numeric. The function using anyelement would require you to cast the three inputs to the same type manually.

43.3.2. ALIAS #

newname ALIAS FOR oldname;

ALIAS 语法比上一部分中提及的更通用：您可以声明任何变量的别名，而不仅仅是函数参数。这在实际中主要是为具有预定名称的变量（例如触发函数中的 NEW 或 OLD）分配不同的名称。

The ALIAS syntax is more general than is suggested in the previous section: you can declare an alias for any variable, not just function parameters. The main practical use for this is to assign a different name for variables with predetermined names, such as NEW or OLD within a trigger function.

示例：

Examples:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

由于 ALIAS 创建了以两种不同的方式命名相同对象的两个方法，因此不受限制地使用它可能会造成混乱。最好仅将其用于覆盖预定名称的目的。

Since ALIAS creates two different ways to name the same object, unrestricted use can be confusing. It’s best to use it only for the purpose of overriding predetermined names.

43.3.3. Copying Types #

variable%TYPE

%TYPE 提供变量或表列的数据类型。您可以用它来声明将持有数据库值变量。例如，假设您在 users 表中有一个名为 user_id 的列。要声明与 users.user_id 具有相同数据类型的变量，您可以编写：

%TYPE provides the data type of a variable or table column. You can use this to declare variables that will hold database values. For example, let’s say you have a column named user_id in your users table. To declare a variable with the same data type as users.user_id you write:

user_id users.user_id%TYPE;

使用 %TYPE 不必知道所引用的结构的数据类型，最重要的是，如果未来引用的项的数据类型发生了改变（例如：将 user_id 的类型从 integer 更改为 real），您可能不需要更改函数定义。

By using %TYPE you don’t need to know the data type of the structure you are referencing, and most importantly, if the data type of the referenced item changes in the future (for instance: you change the type of user_id from integer to real), you might not need to change your function definition.

%TYPE 在多态函数中特别有用，因为内部变量所需的数据类型可能会在从一次调用更改为另一次调用中发生变化。可以通过将 %TYPE 应用于函数的参数或结果占位符来创建适当的变量。

%TYPE is particularly valuable in polymorphic functions, since the data types needed for internal variables can change from one call to the next. Appropriate variables can be created by applying %TYPE to the function’s arguments or result placeholders.

43.3.4. Row Types #

name table_name%ROWTYPE;
name composite_type_name;

复合类型变量称为 row 变量（或 row-type 变量）。只要查询的列集与变量的声明类型匹配，此类变量就可以容纳 SELECT 或 FOR 查询结果的一整行。可以使用通常的点表示法访问行值中的各个字段，例如 rowvar.field。

A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so long as that query’s column set matches the declared type of the variable. The individual fields of the row value are accessed using the usual dot notation, for example rowvar.field.

可以通过使用 table_name_%ROWTYPE_ 表示法来将行变量声明为与现有表或视图的行相同的类型；或者通过给出一个复合类型的名字来进行声明。（由于每个表都有一个具有相同名字的相关联复合类型，因此在 PostgreSQL 中实际上不重要是写 %ROWTYPE 还是没写。但是带有 %ROWTYPE 的形式更便于移植。）

A row variable can be declared to have the same type as the rows of an existing table or view, by using the table_name_%ROWTYPE_ notation; or it can be declared by giving a composite type’s name. (Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write %ROWTYPE or not. But the form with %ROWTYPE is more portable.)

函数的参数可以是组合类型（完整表行）。在那种情况下，相应的标识符 $_n_ 将是行变量，并且可以从中选择字段，例如 $1.user_id 。

Parameters to a function can be composite types (complete table rows). In that case, the corresponding identifier $_n_ will be a row variable, and fields can be selected from it, for example $1.user_id.

下面是一个使用复合类型的示例。table1 和 table2 是至少具有所述字段的现有表：

Here is an example of using composite types. table1 and table2 are existing tables having at least the mentioned fields:

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

43.3.5. Record Types #

name RECORD;

记录变量类似于行类型变量，但是它们没有预定义的结构。它们采用 SELECT 或 FOR 命令在分配时实际的行结构。记录变量的子结构可以在每次被分配时发生变化。这样做的后果是，在记录变量首次被分配之前，它没有子结构，任何尝试访问其中的字段的行为都将引发运行时错误。

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error.

请注意，RECORD 不是真正的类型，仅仅是一个占位符。还应该意识到，当将一个 PL/PgSQL 函数声明为返回 record 类型时，这与记录变量并不是完全相同概念，即使此类函数可能使用一个记录变量来保存它的结果。在这两种情况下，函数在编写时实际的行结构都是未知的，但对于返回 record 的函数，实际的结构是在处理调用查询时确定的，而一个记录变量可以动态地更改它的行结构。

Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly.

43.3.6. Collation of PL/pgSQL Variables #

当 PL/pgSQL 函数具有一个或多个可校对数据类型参数时，将根据分配给实际参数的校对规则为每个函数调用标识一个校对规则，如 Section 24.2 中所述。如果成功地标识出一个校对规则（即，参数之间没有隐式校对规则冲突），那么所有可校对参数都将被视为隐式具有该校对规则。这将影响函数中与校对规则敏感的操作的行为。例如，请考虑

When a PL/pgSQL function has one or more parameters of collatable data types, a collation is identified for each function call depending on the collations assigned to the actual arguments, as described in Section 24.2. If a collation is successfully identified (i.e., there are no conflicts of implicit collations among the arguments) then all the collatable parameters are treated as having that collation implicitly. This will affect the behavior of collation-sensitive operations within the function. For example, consider

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

less_than 的第一个用法将使用 text_field_1 和 text_field_2 的公共排序规则进行比较，而第二个用法将使用 C 排序规则。

The first use of less_than will use the common collation of text_field_1 and text_field_2 for the comparison, while the second use will use C collation.

此外，识别出的排序规则也被认为是任何可排序类型本地变量的排序规则。因此，如果将此函数写成这样，它不会有任何不同的工作方式

Furthermore, the identified collation is also assumed as the collation of any local variables that are of collatable types. Thus this function would not work any differently if it were written as

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

如果没有任何可排序数据类型参数，或者无法为这些参数识别出公共排序规则，那么参数和本地变量将使用其数据类型（通常是数据库的默认排序规则）的默认排序规则，但对于域类型变量来说可能是不同的。

If there are no parameters of collatable data types, or no common collation can be identified for them, then parameters and local variables use the default collation of their data type (which is usually the database’s default collation, but could be different for variables of domain types).

采用可整理数据类型时，可以通过在声明中包含 COLLATE 选项来为局部变量关联一个不同的整理规则，例如

A local variable of a collatable data type can have a different collation associated with it by including the COLLATE option in its declaration, for example

DECLARE
    local_a text COLLATE "en_US";

此选项会覆盖按照上述规则赋予该变量的整理规则。

This option overrides the collation that would otherwise be given to the variable according to the rules above.

当然，如果需要强制在特定操作中使用特定的整理规则，可以在函数中编写明确的 COLLATE 子句。例如：

Also, of course explicit COLLATE clauses can be written inside a function if it is desired to force a particular collation to be used in a particular operation. For example,

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

这将覆盖表达式中使用的表列、参数或局部变量关联的整理规则，就像在纯 SQL 命令中所做的那样。

This overrides the collations associated with the table columns, parameters, or local variables used in the expression, just as would happen in a plain SQL command.