40.3. 声明 · PostgreSQL 中文文档 9.3

# 40.3\. 声明所有在块里使用的变量都必须在一个块的声明段里声明。唯一的例外是一个`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 40.3.6](#calibre_link-1671)）。如果声明了`NOT NULL`，那么赋予NULL的数值将运行时导致错误。所以所有声明为`NOT NULL`的变量还必须声明一个非空的缺省值。缺省值是在每次进入该块的时候计算的，而不是每次调用函数时。因此，如果把`now()`赋予一个类型为`timestamp`的变量会令变量拥有函数实际调用的时间，而不是函数预编译的时间。例如： ``` quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; user_id CONSTANT integer := 10; ``` ## 40.3.1\. 声明函数参数传递给函数的参数都是用`$1`, `$2`等等这样的标识符。为了增加可读性，可以为`$``_n_`参数名声明别名。然后别名或者数字标识符都可以指向参数值。有两种创建别名的方法，比较好的是在`CREATE FUNCTION`命令里给出参数名，比如： ``` CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; ``` 另外一个方法（也是PostgreSQL 8.0以前的唯一的方法），是使用声明语法明确声明别名： ``` _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 这里放一些使用 v_string 和 index 的计算 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` —当然也可以包含它，不过这样就显得多余了。输出参数在返回多个数值的时候非常有用。一个简单的例子是： ``` 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 35.4.4](#calibre_link-913)里面讨论的，这样做实际上为函数的结果创建了一个匿名的记录类型。如果给出一个 `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 quantity, quantity * price FROM sales WHERE itemno = p_itemno; END; $$ LANGUAGE plpgsql; ``` 这完全等价于声明一个或多个`OUT`参数，并且声明`RETURNS SETOF``_sometype_`。如果将PL/pgSQL函数的返回类型声明为多态类型 (`anyelement`, `anyarray`, `anynonarray`, `anyenum`, 或者`anyrange`), 那么就会创建一个特殊的`$0`参数，它的数据类型是函数的实际返回类型，和从实际输入类型的推导类型一样 (参阅[Section 35.2.5](#calibre_link-909))。这样就允许函数像 [Section 40.3.3](#calibre_link-1668)里显示的那样访问它的实际返回类型。 `$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; ``` ## 40.3.2\. `别名` ``` _newname_ ALIAS FOR _oldname_; ``` `别名`语法比在之前章节提到的更普遍：可以为任何一个参数声明别名，而不仅仅只是对函数。这样做的主要目的是为已经有名字的参数重新定义一个名字，例如触发器中的`NEW` 或者`OLD`。例如： ``` DECLARE prior ALIAS FOR old; updated ALIAS FOR new; ``` 由于`ALIAS`创建了两种不同的方式来命名相同的对象，因此，无限制的使用会造成混淆。最好是在重写预定名称时使用。 ## 40.3.3\. 拷贝类型 ``` _variable_%TYPE ``` `%TYPE`提供一个变量或者表字段的数据类型。你可以用这个声明将要保存数据库数值的变量。比如，假如你在`users`表里面有一个`user_id`字段。要声明一个和`users.user_id`类型相同的变量，可以这样写： ``` user_id users.user_id%TYPE; ``` 通过使用`%TYPE`，你无需知道引用的结构的数据类型，并且，最重要的是，如果被引用项的数据类型在将来变化了(比如把`user_id`的类型从`integer` 改成`real`)，也不需要修改函数定义。 `%TYPE`对多态函数特别有用，因为内部变量的数据类型可能在不同调用中不一样。可以通过给函数的参数或者结果占位符附加`%TYPE`的方法来创建合适的变量。 ## 40.3.4\. 行类型 ``` _name_ _table_name_%ROWTYPE; _name_ _composite_type_name_; ``` 一个复合类型变量叫做_行_变量(或者_row-type_变量)。这样的一个变量可以保存一次`SELECT`或者`FOR`命令结果的完整一行，只要命令的字段集匹配该变量声明的类型。行数值的字段使用点表示法访问，比如`rowvar.field`。行变量可以声明为和一个现有的表或者视图的行类型相同，方法是使用`_table_name_``%ROWTYPE`表示法；或者你也可以声明它的类型是一个复合类型的名字。因为每个表都有一个相关联的同名数据类型，在PostgreSQL里实在是无所谓你写不写`%ROWTYPE`。但是有`%ROWTYPE`的形式移植性更好。函数的参数可以是复合类型(表的完整行)。这个时候，对应的标识符`$``_n_`将是一个行变量，并且可以从中选取字段，比如`$1.user_id`。在一个行类型的变量中，只可以访问用户定义的表中行的属性，不包括OID 或者其它系统属性(因为该行可能来自一个视图)。该行类型的数据域继承表中像`char(``_n_`) 这种类型字段的尺寸和精度。这里是一个使用复合类型的例子。`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 ... ; ``` ## 40.3.5\. 记录类型 ``` _name_ RECORD; ``` 纪录变量类似行类型变量，但是它们没有预定义的结构。它们在`SELECT` 或者`FOR`命令中获取实际的行结构。一个行变量的子结构可以在每次赋值的时候改变。这样做的一个结果是：在一个记录变量被赋予数值之前，它没有子结构，并且任何对其中的数据域进行访问的企图都将产生一个运行时错误。请注意，`RECORD`不是真正的数据类型，只是一个占位符。还应该意识到在把一个PL/pgSQL函数声明为返回`record`类型的时候，它和一个记录变量的概念并不完全相同，即使这个函数可能使用一个记录变量保存它的结果也如此。在这两种情况下书写函数的时候，实际的行结构都是未知的，但是对于返回`record`的函数来说，实际的结构是在调用它的查询被分析的时候决定的，而行变量可以在运行中改变其行结构。 ## 40.3.6\. PL/pgSQL变量的排序规则当PL/pgSQL函数有排序规则数据类型的一个以上的参数时，排序规则确定每个函数调用依赖于分配给实际参数的排序规则，正如[Section 22.2](#calibre_link-667)。如果排序规则成功被识别（比如，在这些参数之间没有隐式排序规则冲突），那么所有排序规则参数作为有隐式排序规则对待。这将影响函数内部排序规则敏感操作行为。比如，考虑： ``` 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` 排序规则。此外，被识别的排序规则也被假定为任何局部变量是collatable类型的排序规则。因此这个函数没有任何不同，如果它被写为： ``` 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; ``` 如果没有collatable数据类型的参数，或者没有通用排序规则可以识别他们，那么参数和局部变量使用数据类型的缺省排序规则（这往往是数据库的缺省排序规则，但是可能不同于域类型变量）。 collatable数据类型的局部变量可以有与声明中包含`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命令中一样。