PostgreSQL 教程

Appearance

43.12.PL/pgSQL开发技巧# ​

43.12.1. 引号处理
43.12.2. 额外的编译时和运行时检查

在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. 引号处理# ​

使用CREATE FUNCTION将PL/pgSQL函数代码指定为字符串常量。如果使用普通方式编写字符串常量，即使用单引号将其括起来，那么函数体内的任何单引号都必须加倍；同样，任何反斜杠都必须加倍（假设使用了转义字符串语法）。加倍引号充其量是乏味的，在更复杂的情况下，代码可能会变得完全不可理解，因为你很容易发现自己需要六个或更多相邻的引号。建议你改为将函数体写为“美元引号”字符串常量（请参见第 4.1.2.4 节）。在美元引号方法中，你永远不会对任何引号加倍，而是注意为所需的每个嵌套级别选择不同的美元引号分隔符。例如，你可以将CREATE FUNCTION命令写为

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

在此内部，你可以对 SQL 命令中的简单文字字符串使用引号，并使用$$来分隔你作为字符串组装的 SQL 命令片段。如果你需要引用包含$$的文本，你可以使用$Q$，依此类推。

下表显示了在不使用美元引号的情况下编写引号时必须执行的操作。在将美元引号之前的代码翻译成更易于理解的内容时，这可能很有用。

1 个引号 #

例如，开始和结束函数体

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

在单引号函数体内的任何位置，引号 必须 成对出现。

2 个引号 #

例如，函数体内的字符串常量

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

在美元引号方法中，你只需编写

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

这正是 PL/pgSQL 解析器在任何情况下都会看到的内容。

4 个引号 #

例如，当函数体内的字符串常量中需要一个单引号时

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 个引号 #

例如，当函数体内的字符串中的单引号与该字符串常量的末尾相邻时

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

然后附加到 a_output 的值将是：AND name LIKE 'foobar'。

在美元引号方法中，这将变为

a_output := a_output || $$ AND name LIKE 'foobar'$$
10 个引号 #

当你在字符串常量中需要两个单引号（占 8 个引号）并且它与该字符串常量的末尾相邻（再加 2 个）时。你可能只有在编写生成其他函数的函数时才需要它，如 示例 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. 其他编译时和运行时检查# ​

为了帮助用户在造成危害之前找到简单但常见问题的情况，PL/pgSQL提供了其他*检查*。启用后，根据配置，它们可用于在编译函数期间发出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)