43.3. Declarations

块中使用的所有变量必须在块的声明部分中声明。 (唯一的 exception 是,在一系列整数值上进行迭代的FOR循环的循环变量自动声明为整数变量,同样,在游标结果上进行迭代的FOR循环的循环变量也自动声明为记录变量.)

PL/pgSQL 变量可以具有任何 SQL 数据类型,例如integervarcharchar

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

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子句(如果提供)指定了 Importing 块时分配给变量的初始值。如果未提供DEFAULT子句,则变量将初始化为 SQL 空值。 CONSTANT选项可防止在初始化后将其分配给变量,以便在块的持续时间内其值保持恒定。 COLLATE选项指定用于该变量的排序规则(请参见Section 43.3.6)。如果指定了NOT NULL,则分配空值将导致运行时错误。所有声明为NOT NULL的变量必须具有指定的非空默认值。可以使用等于(=)代替兼容 PL/SQL 的:=

每次 Importing 该块时,都会评估变量的默认值并将其分配给该变量(不仅仅是每个函数调用一次)。因此,例如,将now()分配给类型timestamp的变量会导致该变量具有当前函数调用的时间,而不是具有函数预编译的时间。

Examples:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

43 .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 函数时,将以与普通 Importing 参数相同的方式为输出参数提供$n名称和可选别名。输出参数实际上是一个以 NULL 开头的变量;在执行功能期间应将其分配给它。参数的最终值是返回的值。例如,销售税示例也可以通过以下方式完成:

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

请注意,我们省略了RETURNS real -我们可以包含它,但这将是多余的。

返回多个值时,输出参数最有用。一个简单的例子是:

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;

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

声明 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 函数的返回类型声明为多态类型(anyelementanyarrayanynonarrayanyenumanyrange)时,将创建一个特殊参数$0。它的数据类型是函数的实际返回类型,从实际 Importing 类型推导得出(请参见Section 38.2.5)。这使函数可以访问其实际的返回类型,如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;

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

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

43.3.2. ALIAS

newname ALIAS FOR oldname;

ALIAS语法比上一节中建议的更通用:您可以为任何变量声明别名,而不仅仅是函数参数。这样做的主要实际用途是为具有 sched 名称的变量分配不同的名称,例如触发函数中的NEWOLD

Examples:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

由于ALIAS创建了两种不同的方式来命名同一对象,因此无限制的使用可能会造成混淆。最好仅将其用于覆盖 sched 名称的目的。

43 .3.3. 复制类型

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. 行类型

name table_name%ROWTYPE;
name composite_type_name;

复合类型的变量称为* row 变量(或 row-type *变量)。只要该查询的列集与该变量的声明类型匹配,此类变量就可以容纳SELECTFOR查询结果的整行。使用常规的点符号(例如rowvar.field)访问行值的各个字段。

使用* table_name * %ROWTYPE表示法,可以将行变量声明为与现有表或视图的行具有相同的类型。或者可以通过给出复合类型的名称来声明它。 (由于每个表都具有相同名称的关联复合类型,因此在 PostgreSQL 中实际上是否写入%ROWTYPE都无关紧要.但是%ROWTYPE的形式更易于移植.)

函数的参数可以是复合类型(完整的表行)。在这种情况下,相应的标识符$n将是一个行变量,并且可以从中选择字段,例如$1.user_id

这是使用复合类型的示例。 table1table2是至少具有上述字段的现有表:

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. 记录类型

name RECORD;

记录变量类似于行类型变量,但是它们没有 sched 义的结构。它们采用在SELECTFOR命令期间分配的行的实际行结构。记录变量的子结构可以在每次分配时更改。这样做的结果是,在首次分配记录变量之前,它没有子结构,并且任何尝试访问其中的字段的尝试都会产生运行时错误。

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

43 .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_1text_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 命令中那样。