2024年9月26日: PostgreSQL 17 发布!
支持的版本:当前 (17) / 16 / 15 / 14 / 13 / 12
开发版本:开发版
不支持的版本:11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4

41.12. 在 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 中开发的另一种好方法是使用一个图形用户界面数据库访问工具,该工具可以方便地在过程语言中进行开发。此类工具的一个示例是 pgAdmin,当然还有其他工具。这些工具通常提供方便的功能,例如转义单引号,并使重新创建和调试函数变得更容易。

41.12.1. 引号的处理 #

PL/pgSQL 函数的代码在 CREATE FUNCTION 中指定为字符串文字。如果您以普通方式使用周围的单引号编写字符串文字,则函数体内的任何单引号都必须加倍;同样,任何反斜杠也必须加倍(假设使用了转义字符串语法)。加倍引号充其量是乏味的,在更复杂的情况下,代码可能变得完全难以理解,因为您很容易发现自己需要六个或更多个相邻的引号。建议您改为将函数体编写为 美元符引号 字符串文字(请参阅 第 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 个)时。如果您正在编写一个生成其他函数的函数,如 示例 41.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 中,因为它将在使用前重新加引号。

41.12.2. 额外的编译时和运行时检查 #

为了帮助用户在简单但常见的问题导致损害之前找到这些问题,PL/pgSQL 提供了额外的检查。启用后,根据配置,它们可用于在函数编译期间发出 WARNINGERROR。接收了 WARNING 的函数可以在不产生更多消息的情况下执行,因此建议您在单独的开发环境中进行测试。

在开发和/或测试环境中,建议将 plpgsql.extra_warningsplpgsql.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 在目标变量的数量和源变量的数量不同时抛出 WARNINGERROR

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)

提交更正

如果您在文档中发现任何不正确的内容,与您对特定功能的体验不符,或者需要进一步澄清,请使用 此表单 报告文档问题。