Postgresql 中文操作指南

43.3. Declarations #

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

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

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

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

变量声明的一般语法是：

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 兼容 := 。

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

示例：

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

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

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

43.3.1. Declaring Function Parameters #

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

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

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

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

name ALIAS FOR $n;

这种风格的示例如下：

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_可以用该标签限定。)

更多一些示例：

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 开始的变量；它应在函数执行期间被赋值。参数的最终值即是返回的值。例如，也可以通过以下方式完成销售税示例：

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

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

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

SELECT sales_tax(100.00);

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

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 。

这也适用于过程，例如：

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：

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

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

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

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_ 完全相同。

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

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 参数；输出参数本身具有相同目的。例如：

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

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

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

借助此示例，如下调用：

SELECT add_three_values(1, 2, 4.7);

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

43.3.2. ALIAS #

newname ALIAS FOR oldname;

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

示例：

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

43.3.3. Copying Types #

variable%TYPE

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

user_id users.user_id%TYPE;

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

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

43.3.4. Row Types #

name table_name%ROWTYPE;
name composite_type_name;

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

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

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

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

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

43.3.6. Collation of PL/pgSQL Variables #

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

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 排序规则。

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

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;

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

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

DECLARE
    local_a text COLLATE "en_US";

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

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

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

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