Chapter 41. PL/pgSQL — SQL Procedural Language Home

Daniel的万事通杂货铺

于 2025-01-10 18:11:00 发布

阅读量62

点赞数

文章标签： sql 前端 javascript

原文链接：https://www.postgresql.org/docs/17/plpgsql-declarations.html

版权

41.3。声明

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

PL/pgSQL变量可以具有任何 SQL 数据类型，例如integer、varchar和char。

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

<span style="color:#000000"><span style="background-color:var(--doccontent-pre-code-bg-color)">user_id 整数；
数量数字（5）；
url varchar;
myrow 表名%ROWTYPE;
myfield 表名.列名%TYPE;
箭头记录；
</span></span>

变量声明的一般语法是：

<span style="color:#000000"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>name</code></em>[常量] <em><code>type</code></em>[整理<em><code>collation_name</code></em> ][非空][ {默认| := | =}<em><code>expression</code></em>  ];
</span></span>

该DEFAULT子句（如果给定）指定输入块时分配给变量的初始值。如果DEFAULT未给出该子句，则变量将初始化为SQL null 值。该CONSTANT选项可防止变量在初始化后被赋值，以便其值在块的持续时间内保持不变。该COLLATE选项指定用于变量的排序规则（请参见第 41.3.6 节）。如果NOT NULL指定，则分配空值会导致运行时错误。所有声明为的变量都NOT NULL必须指定一个非空默认值。可以使用Equal ( =) 代替符合 PL/SQL 的:=。

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

示例：

<span style="color:#000000"><span style="background-color:var(--doccontent-pre-code-bg-color)">数量整数 DEFAULT 32；
url varchar := 'http://mysite.com';
transaction_time 带时区的常量时间戳 := now();
</span></span>

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

<span style="color:#000000"><span style="background-color:var(--doccontent-pre-code-bg-color)">宣布
  x 整数 := 1;
  y 整数 := x + 1;
</span></span>

41.3.1。声明函数参数

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

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 sales_tax(实数小计) 返回实数 AS $$
开始
    返回小计 * 0.06；
结尾;
$$ 语言 plpgsql;
</span></span></span>

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>name</code></em>$ 的别名<em><code>n</code></em>；
</span></span></span>

这种风格的相同示例如下所示：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 sales_tax(real) 返回 real AS $$
宣布
    别名小计 1 美元；
开始
    返回小计 * 0.06；
结尾;
$$ 语言 plpgsql;
</span></span></span>

笔记

这两个例子并不完全等同。在第一种情况下，subtotal可以被引用为sales_tax.subtotal，但在第二种情况下则不能。（如果我们将标签附加到内部块，subtotal则可以使用该标签进行限定。）

更多示例：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 instr(varchar, 整数) 返回整数 AS $$
宣布
    v_string $1 的别名；
    索引别名为 2 美元；
开始
    -- 这里使用 v_string 和索引进行一些计算
结尾;
$$ 语言 plpgsql;


创建函数 concat_selected_fields(in_t sometablename) 返回文本 AS $$
开始
    返回 in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7；
结尾;
$$ 语言 plpgsql;
</span></span></span>

当使用输出参数声明PL/pgSQL函数时，输出参数的名称和可选别名与普通输入参数的方式相同。输出参数实际上是一个以 NULL 开头的变量；它应该在函数执行期间分配给。参数的最终值就是返回的值。例如，销售税示例也可以这样完成：$n

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 sales_tax(实际小计, OUT 实际税) AS $$
开始
    税费 := 小计 * 0.06;
结尾;
$$ 语言 plpgsql;
</span></span></span>

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

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">选择 sales_tax(100.00);
</span></span></span>

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
开始
    总和：= x + y；
    产品 := x * y;
结尾;
$$ 语言 plpgsql;

从 sum_n_product(2, 4) 中选择*；
 总和|产品
------+------
   6 | 8
</span></span></span>

正如第 36.5.4 节中所讨论的，这有效地为函数的结果创建了一个匿名记录类型。如果RETURNS给出一个子句，它必须说RETURNS record。

这也适用于过程，例如：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建过程 sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
开始
    总和：= x + y；
    产品 := x * y;
结尾;
$$ 语言 plpgsql;
</span></span></span>

在调用过程时，必须指定所有参数。对于输出参数，NULL可以在从普通 SQL 调用过程时指定：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">调用 sum_n_product(2, 4, NULL, NULL);
 总和|产品
------+------
   6 | 8
</span></span></span>

然而，当从PL/pgSQL调用过程时，您应该为任何输出参数编写一个变量；该变量将接收调用的结果。详细信息请参见第 41.6.3 节。

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数extend_sales(p_itemno int)
返回表（数量 int，总数值） AS $$
开始
    返回查询 SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
结尾;
$$ 语言 plpgsql;
</span></span></span>

OUT这与声明一个或多个参数并指定完全相同。RETURNS SETOF sometype

当PL/pgSQL函数的返回类型被声明为多态类型时（参见第 36.2.5 节$0），将创建一个特殊参数。它的数据类型是函数的实际返回类型，从实际输入类型推导出来。这允许函数访问其实际返回类型，如第 41.3.3 节所示。$0初始化为 null 并且可以由函数修改，因此如果需要，它可以用于保存返回值，但这不是必需的。$0也可以指定别名。例如，此函数适用于具有+运算符的任何数据类型：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 add_two_values(v1 任意元素, v2 任意元素, v3 任意元素)
返回任意元素 AS $$
宣布
    结果 ALIAS FOR $0;
开始
    结果：= v1 + v2 + v3；
    返回结果；
结尾;
$$ 语言 plpgsql;
</span></span></span>

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 add_two_values(v1 任意元素, v2 任意元素, v3 任意元素,
                                 OUT 任意元素之和）
作为$$
开始
    总和：= v1 + v2 + v3；
结尾;
$$ 语言 plpgsql;
</span></span></span>

实际上，使用类型族声明多态函数可能更有用anycompatible，以便将输入参数自动提升为通用类型。例如：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 add_two_values(v1 任意兼容、v2 任意兼容、v3 任意兼容)
返回任何兼容的 AS $$
开始
    返回 v1 + v2 + v3；
结尾;
$$ 语言 plpgsql;
</span></span></span>

在此示例中，诸如以下的调用

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">选择 add_two_values(1, 2, 4.7);
</span></span></span>

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

41.3.2。 `ALIAS`

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>newname</code></em>别名为<em><code>oldname</code></em>;
</span></span></span>

该ALIAS语法比上一节中建议的更通用：您可以为任何变量声明别名，而不仅仅是函数参数。其主要实际用途是为具有预定名称的变量分配不同的名称，例如触发器函数中的变量NEW或OLD触发器函数内的变量。

示例：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">宣布
  旧的旧别名；
  更新了新的别名；
</span></span></span>

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

41.3.3。复制类型

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>name</code></em> <em><code>table</code></em>。<em><code>column</code></em>%类型
%类型
<em><code>name</code></em> <em><code>variable</code></em></span></span></span>

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">user_id users.user_id%TYPE;
</span></span></span>

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

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">user_ids users.user_id%TYPE[];
user_ids users.user_id%TYPE ARRAY[4]; -- 相当于上面的
</span></span></span>

正如将表列声明为数组时一样，编写多个括号对或特定数组维度并不重要：PostgreSQL将给定元素类型的所有数组视为相同类型，无论维度如何。（参见第 8.15.1 节。）

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

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

41.3.4。行类型

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>name</code></em> <em><code>table_name</code></em><code>%ROWTYPE</code>;
 ;
<em><code>name</code></em> <em><code>composite_type_name</code></em></span></span></span>

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

可以使用以下表示法将行变量声明为与现有表或视图的行具有相同的类型；或者可以通过给出复合类型的名称来声明它。（由于每个表都有一个关联的同名复合类型，所以在PostgreSQL中，写与不写实际上并不重要。但是 with 的形式更加可移植。）table_name%ROWTYPE%ROWTYPE%ROWTYPE

与一样%TYPE，%ROWTYPE后面可以跟上数组修饰来声明一个保存所引用复合类型数组的变量。

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

这是使用复合类型的示例。table1并且table2是至少具有上述字段的现有表：

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 merge_fields(t_row table1) 返回文本 AS $$
宣布
    t2_row 表2%ROWTYPE;
开始
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    返回 t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
结尾;
$$ 语言 plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
</span></span></span>

41.3.5。记录类型

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)"><em><code>name</code></em>记录;
</span></span></span>

记录变量与行类型变量类似，但它们没有预定义的结构。它们采用SELECTor命令期间分配的行的实际行结构FOR。记录变量的子结构在每次分配时都会发生变化。这样做的结果是，在第一次分配记录变量之前，它没有子结构，任何访问其中字段的尝试都会引发运行时错误。

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

41.3.6。PL/pgSQL变量的整理

当PL/pgSQL函数具有一个或多个可排序数据类型的参数时，根据分配给实际参数的排序规则为每个函数调用标识排序规则，如第 23.2 节中所述。如果成功识别排序规则（即，参数之间不存在隐式排序规则冲突），则所有可排序参数都将被视为隐式具有该排序规则。这将影响函数内排序敏感操作的行为。例如，考虑

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 less_than(a text, b text) 返回 boolean AS $$
开始
    返回 a < b;
结尾;
$$ 语言 plpgsql;

从表 1 中选择 less_than(text_field_1, text_field_2)；
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
</span></span></span>

第一次使用将使用和的less_than通用排序规则进行比较，而第二次使用将使用排序规则。text_field_1text_field_2C

此外，所识别的排序规则还被假定为可排序类型的任何局部变量的排序规则。因此，如果将这个函数写成这样，它的工作方式不会有任何不同

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 less_than(a text, b text) 返回 boolean AS $$
宣布
    local_a 文本 := a;
    local_b 文本 := b;
开始
    返回 local_a < local_b;
结尾;
$$ 语言 plpgsql;
</span></span></span>

如果没有可排序数据类型的参数，或者无法为它们标识通用排序规则，则参数和局部变量将使用其数据类型的默认排序规则（通常是数据库的默认排序规则，但对于域变量可能不同）类型）。

可排序数据类型的局部变量可以通过在其声明中包含选项来具有与其关联的不同排序规则COLLATE，例如

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">宣布
    local_a 文本整理“en_US”；
</span></span></span>

此选项会覆盖根据上述规则赋予变量的排序规则。

此外，COLLATE如果需要强制在特定操作中使用特定排序规则，当然可以在函数内编写显式子句。例如，

<span style="color:#000000"><span style="color:var(--doccontent-pre-code-fg-color) !important"><span style="background-color:var(--doccontent-pre-code-bg-color)">创建函数 less_than_c(a text, b text) 返回 boolean AS $$
开始
    返回 a < b 整理“C”；
结尾;
$$ 语言 plpgsql;</span></span></span>