Postgresql 中文操作指南

43.12. Tips for Developing in PL/pgSQL #

使用 PL/pgSQL 开发的一种好方法是在文本编辑器中创建函数，在另一个窗口中使用 psql 来加载并测试这些函数。如果您以这种方式进行操作，则最好使用 CREATE OR REPLACE FUNCTION 编写函数。这样，您只需重新加载文件即可更新函数定义。例如：

One good way to develop in PL/pgSQL is to use the text editor of your choice to create your functions, and in another window, use psql to load and test those functions. If you are doing it this way, it is a good idea to write the function using CREATE OR REPLACE FUNCTION. That way you can just reload the file to update the function definition. For example:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;

在运行 psql 时，您可以使用以下命令加载或重新加载此类函数定义文件：

While running psql, you can load or reload such a function definition file with:

\i filename.sql

然后立即发出 SQL 命令来测试该函数。

and then immediately issue SQL commands to test the function.

使用 PL/pgSQL 开发的另一种好方法是使用 GUI 数据库访问工具，该工具有助于以过程语言进行开发。这种工具的一个例子是 pgAdmin，尽管还有其他工具。这些工具通常提供方便的功能，例如转义单引号并使其更容易重新创建和调试函数。

Another good way to develop in PL/pgSQL is with a GUI database access tool that facilitates development in a procedural language. One example of such a tool is pgAdmin, although others exist. These tools often provide convenient features such as escaping single quotes and making it easier to recreate and debug functions.

43.12.1. Handling of Quotation Marks #

PL/pgSQL 函数的代码指定在 _CREATE FUNCTION_中，作为字符串文本。如果你用普通方法编写字符串文本，并使用单引号将其包围，则函数体内的任何单引号都必须加倍；同样，任何反斜杠都必须加倍（假设使用了转义字符串语法）。加倍引号充其量是乏味的，而且在更复杂的情况下，代码可能会变得难以理解，因为你很容易发现自己需要六个或更多相邻的引号。建议你改为将函数体写为“美元引号”字符串文字（请参阅 Section 4.1.2.4）。在美元引用方法中，你永远不会加倍任何引号，而要仔细选择所需的每个嵌套级别不同的美元引用分隔符。例如，你可以将 _CREATE FUNCTION_命令写成：

The code of a PL/pgSQL function is specified in CREATE FUNCTION as a string literal. If you write the string literal in the ordinary way with surrounding single quotes, then any single quotes inside the function body must be doubled; likewise any backslashes must be doubled (assuming escape string syntax is used). Doubling quotes is at best tedious, and in more complicated cases the code can become downright incomprehensible, because you can easily find yourself needing half a dozen or more adjacent quote marks. It’s recommended that you instead write the function body as a “dollar-quoted” string literal (see Section 4.1.2.4). In the dollar-quoting approach, you never double any quote marks, but instead take care to choose a different dollar-quoting delimiter for each level of nesting you need. For example, you might write the CREATE FUNCTION command as:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;

在此，您可以在 SQL 命令中对简单的文字字符串使用引号和 _ to delimit fragments of SQL commands that you are assembling as strings. If you need to quote text that includes ，可以使用 _$Q$，依此类推。

Within this, you might use quote marks for simple literal strings in SQL commands and to delimit fragments of SQL commands that you are assembling as strings. If you need to quote text that includes , you could use $Q$, and so on.

下表显示了在不使用美元引用时编写引号时必须执行的操作。在将美元引用前的代码转换为更易理解的内容时，它可能很有用。

The following chart shows what you have to do when writing quote marks without dollar quoting. It might be useful when translating pre-dollar quoting code into something more comprehensible.

  • 1 quotation mark #

    • To begin and end the function body, for example:

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

  • Anywhere within a single-quoted function body, quote marks must appear in pairs.

    • 2 quotation marks #

  • For string literals inside the function body, for example:

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

  • In the dollar-quoting approach, you’d just write:

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

  • which is exactly what the PL/pgSQL parser would see in either case.

    • 4 quotation marks #

  • When you need a single quotation mark in a string constant inside the function body, for example:

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

  • The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz.

  • In the dollar-quoting approach, you’d write:

a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

  • being careful that any dollar-quote delimiters around this are not just $$.

    • 6 quotation marks #

  • When a single quotation mark in a string inside the function body is adjacent to the end of that string constant, for example:

a_output := a_output || '' AND name LIKE ''''foobar''''''

  • The value appended to a_output would then be: AND name LIKE 'foobar'.

  • In the dollar-quoting approach, this becomes:

a_output := a_output || $$ AND name LIKE 'foobar'$$

  • 10 quotation marks #

    • When you want two single quotation marks in a string constant (which accounts for 8 quotation marks) and this is adjacent to the end of that string constant (2 more). You will probably only need that if you are writing a function that generates other functions, as in Example 43.10. For example:

a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';

  • The value of a_output would then be:

if v_... like ''...'' then return ''...''; end if;

  • In the dollar-quoting approach, this becomes:

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;

  • where we assume we only need to put single quote marks into a_output, because it will be re-quoted before use.

43.12.2. Additional Compile-Time and Run-Time Checks #

为了帮助用户在问题造成损害前发现简单但常见问题的实例，PL/pgSQL 提供了其他 checks。启用后，根据配置，它们可用于在编译函数期间发出 WARNING 或 ERROR。收到 WARNING 的函数可以在不产生进一步消息的情况下执行，因此建议您在单独的开发环境中进行测试。

To aid the user in finding instances of simple but common problems before they cause harm, PL/pgSQL provides additional checks. When enabled, depending on the configuration, they can be used to emit either a WARNING or an ERROR during the compilation of a function. A function which has received a WARNING can be executed without producing further messages, so you are advised to test in a separate development environment.

在开发和/或测试环境中，适当将 plpgsql.extra_warnings 或 plpgsql.extra_errors 设置为 "all" 是值得鼓励的。

Setting plpgsql.extra_warnings, or plpgsql.extra_errors, as appropriate, to "all" is encouraged in development and/or testing environments.

通过配置变量 plpgsql.extra_warnings（警告）和 plpgsql.extra_errors（错误）启用这些附加检查。两者都可以设置为检查的逗号分隔列表 "none" 或 "all"。默认值为 "none"。当前，可用检查列表包括：

These additional checks are enabled through the configuration variables plpgsql.extra_warnings for warnings and plpgsql.extra_errors for errors. Both can be set either to a comma-separated list of checks, "none" or "all". The default is "none". Currently the list of available checks includes:

  • shadowed_variables #

    • Checks if a declaration shadows a previously defined variable.

  • strict_multi_assignment #

    • Some PL/pgSQL commands allow assigning values to more than one variable at a time, such as SELECT INTO. Typically, the number of target variables and the number of source variables should match, though PL/pgSQL will use NULL for missing values and extra variables are ignored. Enabling this check will cause PL/pgSQL to throw a WARNING or ERROR whenever the number of target variables and the number of source variables are different.

  • too_many_rows #

    • Enabling this check will cause PL/pgSQL to check if a given query returns more than one row when an INTO clause is used. As an INTO statement will only ever use one row, having a query return multiple rows is generally either inefficient and/or nondeterministic and therefore is likely an error.

以下示例显示将 plpgsql.extra_warnings 设置为 shadowed_variables 的效果：

The following example shows the effect of plpgsql.extra_warnings set to shadowed_variables:

SET plpgsql.extra_warnings TO 'shadowed_variables';

CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END;
$$ LANGUAGE plpgsql;
WARNING:  variable "f1" shadows a previously defined variable
LINE 3: f1 int;
        ^
CREATE FUNCTION

以下示例显示将 plpgsql.extra_warnings 设置为 strict_multi_assignment 的效果：

The below example shows the effects of setting plpgsql.extra_warnings to strict_multi_assignment:

SET plpgsql.extra_warnings TO 'strict_multi_assignment';

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
AS $$
DECLARE
  x int;
  y int;
BEGIN
  SELECT 1 INTO x, y;
  SELECT 1, 2 INTO x, y;
  SELECT 1, 2, 3 INTO x, y;
END;
$$;

SELECT foo();
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.

 foo
-----

(1 row)