9.3 9.4 9.5 9.6 10 11 12 13
阿里云PostgreSQL 问题报告 纠错本页面

40.3. 声明

所有在块里使用的变量都必须在一个块的声明段里声明。 唯一的例外是一个FOR循环里的循环变量是在一个整数范围内迭代的, 被自动声明为整数变量。并且同样从游标结果中FOR循环迭代的循环变量自动被声明为记录变量。

PL/pgSQL变量可以使用任意的SQL数据类型,比如integer, varcharchar等等。

下面是一些变量声明的例子:

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选项声明变量使用的排序规则(参见第 40.3.6 节)。 如果声明了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;

注意: 这两个例子的作用不是完全一致的。 在第一个例子中,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;

正如在第 35.4.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 quantity, quantity * price FROM sales
                 WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

这完全等价于声明一个或多个OUT参数, 并且声明RETURNS SETOFsometype

如果将PL/pgSQL函数的返回类型声明为多态类型 (anyelement, anyarray, anynonarray, anyenum, 或者anyrange), 那么就会创建一个特殊的$0参数, 它的数据类型是函数的实际返回类型, 和从实际输入类型的推导类型一样 (参阅第 35.2.5 节)。这样就允许函数像 第 40.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;

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) 这种类型字段的尺寸和精度。

这里是一个使用复合类型的例子。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 ... ;

40.3.5. 记录类型

name RECORD;

纪录变量类似行类型变量,但是它们没有预定义的结构。它们在SELECT 或者FOR命令中获取实际的行结构。 一个行变量的子结构可以在每次赋值的时候改变。 这样做的一个结果是:在一个记录变量被赋予数值之前,它没有子结构, 并且任何对其中的数据域进行访问的企图都将产生一个运行时错误。

请注意,RECORD不是真正的数据类型,只是一个占位符。 还应该意识到在把一个PL/pgSQL函数声明为返回record类型的时候,它和一个记录变量的概念并不完全相同, 即使这个函数可能使用一个记录变量保存它的结果也如此。 在这两种情况下书写函数的时候,实际的行结构都是未知的, 但是对于返回record的函数来说, 实际的结构是在调用它的查询被分析的时候决定的,而行变量可以在运行中改变其行结构。

40.3.6. PL/pgSQL变量的排序规则

PL/pgSQL函数有排序规则数据类型的一个以上的参数时, 排序规则确定每个函数调用依赖于分配给实际参数的排序规则,正如第 22.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 排序规则。

此外,被识别的排序规则也被假定为任何局部变量是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命令中一样。

<
/BODY >