Postgresql 中文操作指南

43.2. Structure of PL/pgSQL #

通过执行 CREATE FUNCTION 命令向服务器定义使用 PL/pgSQL 编写的函数。这样的命令通常看起来像，比方说

Functions written in PL/pgSQL are defined to the server by executing CREATE FUNCTION commands. Such a command would normally look like, say,

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

对于_CREATE FUNCTION_，函数体只是一个字符串文本。通常使用美元引用（见 Section 4.1.2.4）编写函数体，而不是普通的单引号语法很有帮助。如果没有美元引用，函数体中的任何单引号或反斜杠都必须通过双写来转义。本章中几乎所有示例都使用美元引用文本作为它们的函数体。

The function body is simply a string literal so far as CREATE FUNCTION is concerned. It is often helpful to use dollar quoting (see Section 4.1.2.4) to write the function body, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function body must be escaped by doubling them. Almost all the examples in this chapter use dollar-quoted literals for their function bodies.

PL/pgSQL 是一种块结构语言。函数体的完整文本必须是一个 block。块的定义如下：

PL/pgSQL is a block-structured language. The complete text of a function body must be a block. A block is defined as:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

块中的每个声明和每个语句都以分号结尾。如上所示，显示在另一个块中的块必须在 END 后面有分号；但是，作为函数体结尾的最终 END 则不需要分号。

Each declaration and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after END, as shown above; however the final END that concludes a function body does not require a semicolon.

Tip

一个常见的错误是在 BEGIN 之后立即写分号。这是不正确的，将导致语法错误。

A common mistake is to write a semicolon immediately after BEGIN. This is incorrect and will result in a syntax error.

只有当您希望为 EXIT 语句中使用而标示块，或对块中声明的变量的名称进行限定时，才需要 label。如果在 END 后给出了标签，则它必须与块开始时的标签相匹配。

A label is only needed if you want to identify the block for use in an EXIT statement, or to qualify the names of the variables declared in the block. If a label is given after END, it must match the label at the block’s beginning.

所有关键字都区分大小写。标识符会隐式转换为小写（除非它们用双引号引起来），就像在普通 SQL 命令中一样。

All key words are case-insensitive. Identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.

在 PL/pgSQL 代码中，注释的工作方式与普通 SQL 中相同。双破折号（—）用于开始注释，该注释一直持续到该行的结尾。/ starts a block comment that extends to the matching occurrence of /。块注释可嵌套。

Comments work the same way in PL/pgSQL code as in ordinary SQL. A double dash (—) starts a comment that extends to the end of the line. A / starts a block comment that extends to the matching occurrence of /. Block comments nest.

块的语句部分中的任何语句都可以是一个 subblock。子块可用于逻辑分组或将变量定位到少量语句。在一个子块中声明的变量在该子块的持续时间内将屏蔽外部块的任何同名变量；但只要您用块的标签限定它们的名称，您仍然可以访问外部变量。例如：

Any statement in the statement section of a block can be a subblock. Subblocks can be used for logical grouping or to localize variables to a small group of statements. Variables declared in a subblock mask any similarly-named variables of outer blocks for the duration of the subblock; but you can access the outer variables anyway if you qualify their names with their block’s label. For example:

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

Note

实际上，任何 PL/pgSQL 函数的主体周围都存在一个隐藏的“外部块”。此块提供函数的参数声明（如果有），以及一些特殊变量，例如 FOUND（请参阅 Section 43.5.5）。外部块标记有函数的名称，这意味着可以使用函数名称限定参数和特殊变量。

There is actually a hidden “outer block” surrounding the body of any PL/pgSQL function. This block provides the declarations of the function’s parameters (if any), as well as some special variables such as FOUND (see Section 43.5.5). The outer block is labeled with the function’s name, meaning that parameters and special variables can be qualified with the function’s name.

重要的是不要混淆使用_BEGIN_/END_来将 PL/pgSQL 中的语句分组与事务控制的相似名称的 SQL 命令。PL/pgSQL 的 _BEGIN/END 仅用于分组；它们不开始或结束事务。有关在 PL/pgSQL 中管理事务的信息，请参阅 Section 43.8。此外，包含_EXCEPTION_子句的块将有效地形成一个可以回滚的子事务，而不会影响外部事务。有关更多信息，请参阅 Section 43.6.8。

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL’s BEGIN/END are only for grouping; they do not start or end a transaction. See Section 43.8 for information on managing transactions in PL/pgSQL. Also, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 43.6.8.