Postgresql 中文操作指南

43.12. Tips for Developing in PL/pgSQL #

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

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

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

\i filename.sql

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

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

43.12.1. Handling of Quotation Marks #

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

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$，依此类推。

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

1 quotation mark #
- 例如，要开始和结束函数体：

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

在单引号函数体中的任何位置，引号 must 成对出现。
- 2 quotation marks #
例如，对于函数体内的字符串文字：

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

在采用美元符号引用法时，您只需编写：

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

这正是 PL/pgSQL 解析器在任何情况下都会看到的内容。
- 4 quotation marks #
例如，当您需要在函数体内的字符串常量中使用单个引号时：

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

实际上附加到 a_output 的值将是：AND name LIKE 'foobar' AND xyz。
在采用美元符号引用法时，您会编写：

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

小心引用这周围的单引号分隔符，而不仅仅是 $$。
- 6 quotation marks #
例如，对于函数体内字符串中紧靠该字符串常量末尾的单引号：

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

添加到 a_output 的值将是：AND name LIKE 'foobar'。
在单引号方式中，这将变成：

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

10 quotation marks #
- 当你想要在字符串常量中使用两个单引号（这占用了 8 个引号）并且它与该字符串常量的末尾相邻（再加 2 个引号）时。你可能只需要在编写生成其他函数的函数（例如 Example 43.10）时用到该特性。例如：

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

a_output 的值将是：

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

在单引号方式中，这将变成：

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

其中我们假设我们只需要在 a_output 中放入单引号，因为它会在使用前重新引用。

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

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

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

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

shadowed_variables #
- 检查声明是否隐藏了以前定义的变量。
strict_multi_assignment #
- 某些 PL/pgSQL 命令允许一次为多个变量分配值，例如 SELECT INTO。通常，目标变量的数量和源变量的数量应匹配，虽然 PL/pgSQL 将使用 NULL 获得缺失值，但将忽略额外的变量。启用此检查将导致 PL/pgSQL 在目标变量的数量和源变量的数量不同时抛出 WARNING 或 ERROR。
too_many_rows #
- 启用此检查将导致 PL/pgSQL 检查在使用 INTO 子句时给定的查询是否返回多行。因为 INTO 语句将只使用一行，所以让查询返回多行通常是低效和/或非确定性的，因此可能是一个错误。

以下示例显示将 plpgsql.extra_warnings 设置为 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 的效果：

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)