41.3. 变量声明

41.3. 变量声明
上一页	上一级	Chapter 41. PL/pgSQL：SQL 过程语言	首页	下一页

41.3. 变量声明 #

41.3.1. 声明函数参数
41.3.2. ALIAS 别名声明
41.3.3. 复制既有类型
41.3.4. 行类型
41.3.5. Record 记录类型
41.3.6. PL/pgSQL 变量的排序规则

块中使用的所有变量都必须在块的声明节中声明。（唯一的例外是：遍历整数范围的 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 空值。 CONSTANT 选项防止变量在初始化之后被赋值，从而使它的值在块的持续期间保持不变。 COLLATE 选项指定用于该变量的排序规则（见 Section 41.3.6）。如果指定了 NOT NULL，则为该变量赋空值将导致运行时错误。所有声明为 NOT 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;

41.3.1. 声明函数参数 #

传递给函数的参数用标识符 $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 36.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 41.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 36.2.5），会创建一个特殊参数 $0。它的数据类型是函数的实际返回类型，由实际输入类型推导而来。这允许函数访问其实际返回类型，如 Section 41.3.3 所示。 $0 被初始化为空值，并且可以被函数修改，因此如果需要可以用它来保存返回值，尽管这并非必需。 $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;

通过把一个或多个输出参数声明为多态类型，也可以获得同样的效果。在这种情况下不使用特殊参数 $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);

将会正常工作，整数输入会被自动提升为 numeric。使用 anyelement 的函数则要求你手工把三个输入转换为相同的类型。

41.3.2. ALIAS 别名声明 #

newname ALIAS FOR oldname;

ALIAS 语法比上一节所暗示的更为通用：你可以为任何变量声明别名，而不仅仅是函数参数。它的主要实际用途是为具有预定名字的变量赋予另一个名字，例如触发器函数中的 NEW 或 OLD。

示例：

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

由于 ALIAS 为同一个对象创建了两种不同的命名方式，不加限制地使用可能造成混淆。最好只在覆盖预定名字这一用途上使用它。

41.3.3. 复制既有类型 #

name table.column%TYPE
name variable%TYPE

%TYPE 提供一个表列或一个先前声明的 PL/pgSQL 变量的数据类型。你可以用它来声明将要保存数据库值的变量。例如，假设你的 users 表中有一个名为 user_id 的列。要声明一个与 users.user_id 具有相同数据类型的变量，你可以写：

user_id users.user_id%TYPE;

也可以在 %TYPE 之后书写数组修饰，从而创建一个保存所引用类型数组的变量：

user_ids users.user_id%TYPE[];
user_ids users.user_id%TYPE ARRAY[4];  -- equivalent to the above

与声明数组表列时一样，写多对方括号还是具体的数组维度都无所谓： PostgreSQL 把给定元素类型的所有数组视为同一类型，无论其维度如何。（见 Section 8.15.1。）

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

%TYPE 在多态函数中尤其有价值，因为内部变量所需的数据类型可能在每次调用之间变化。通过对函数的参数或结果占位符应用 %TYPE，可以创建合适的变量。

41.3.4. 行类型 #

name table_name%ROWTYPE;
name composite_type_name;

复合类型的变量被称为行变量（或行类型变量）。这样的变量可以保存 SELECT 或 FOR 查询结果的整行，只要该查询的列集合与变量声明的类型相匹配。行值的各个字段可以使用常用的点号记法访问，例如 rowvar.field。

可以使用 table_name%ROWTYPE 记法把行变量声明为与现有表或视图的行具有相同的类型；也可以通过给出复合类型的名称来声明它。（由于每个表都有一个同名的关联复合类型，在 PostgreSQL 中实际上写不写 %ROWTYPE 都可以。但带 %ROWTYPE 的形式可移植性更好。）

与 %TYPE 一样，%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 ... ;

41.3.5. Record 记录类型 #

name RECORD;

记录（record）变量与行类型变量类似，但它们没有预定义的结构。它们会采用在 SELECT 或 FOR 命令期间被赋给它们的那一行的实际行结构。记录变量的子结构在每次被赋值时都可以变化。由此带来的一个后果是：在记录变量第一次被赋值之前，它没有子结构，任何访问其中字段的尝试都会导致运行时错误。

注意 RECORD 并不是一个真正的数据类型，只是一个占位符。还应意识到，当一个 PL/pgSQL 函数被声明为返回 record 类型时，这与记录变量的概念并不完全相同，即使这样的函数可能使用记录变量来保存其结果。在这两种情况下，编写函数时实际的行结构都是未知的；但对于返回 record 的函数，实际结构是在解析调用查询时确定的，而记录变量可以随时改变它的行结构。

41.3.6. PL/pgSQL 变量的排序规则 #

当 PL/pgSQL 函数有一个或多个可排序数据类型的参数时，每次函数调用都会根据实际参数被赋予的排序规则确定一个排序规则，如 Section 23.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 命令中发生的那样。