处理PostgreSQL异常的优雅方式？

Question

在PostgreSQL中，我想创建一个安全封装机制，如果发生异常则返回空结果。考虑以下内容：

In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following:

结果翻译为:

在PostgreSQL中，我想创建一个安全的包装机制，如果发生异常，则返回空结果。考虑以下情况：

SELECT * FROM myschema.mytable;

我可以在客户端应用程序中进行安全包装：

try {
    result = execute_query('SELECT value FROM myschema.mytable').fetchall();
}
catch(pg_exception) {
    result = []
}

我能否直接在SQL中做这样的事情？我想使以下代码起作用，但似乎应该将其放入DO $$ ... $$块中，而在这里我迷失了方向。

BEGIN
    SELECT * FROM myschema.mytable;
EXCEPTION WHEN others THEN
    SELECT unnest(ARRAY[]::TEXT[])
END

- Tregoreg

---

DO 不能返回任何内容。您必须使用存储过程（使用语言 plpgsql 进行异常处理）。-- 同样，没有返回行 不等于 返回一个带有空数组的单行（即在 js 中，这意味着 [] != [{col1:[]}]）。 - pozs

@posz 很抱歉，我在示例中添加了 unnest 以产生所需的行为（不知道如何更优雅地实现）。无论如何，每次执行带有异常处理的查询时，我都必须声明过程吗？没有其他选项吗？ - Tregoreg

所以你想防范任何和所有的异常，还是只是担心表可能不存在？你想返回什么？来自单个列“value”的单个值？还是一组行？这是为一个硬编码的表名还是多个可能的表名？ - Erwin Brandstetter

@Tregoreg 不，如果你只想在简单查询中进行异常处理，通常是由客户端而不是服务器完成的。plpgsql的异常处理主要用于存储的“逻辑”。但你害怕什么类型的异常？也许有一种替代方案。 - pozs

@Tregoreg：抱歉，编辑标题时我似乎做了太多假设。 - Erwin Brandstetter

Answer 1

PL/pgSQL中的异常处理

PL/pgSQL代码总是被包装在BEGIN ... END块中。它可以在DO语句或函数的主体内部。块可以嵌套在内部，但不能存在于外部，请不要与普通SQL混淆。

每个块都可以选择包含一个EXCEPTION子句来处理异常，但需要捕获异常的函数更加昂贵，因此最好避免异常。Postgres需要准备回滚到异常发生之前的事务点，类似于SQL SAVEPOINT。手册：

一个包含 EXCEPTION 子句的块比没有该子句的块进入和退出成本更高。因此，除非必要，不要使用 EXCEPTION。

示例：

• 函数中的 SELECT 或 INSERT 是否容易出现竞态条件？

如何在示例中避免异常

DO 语句不能返回任何内容。创建一个函数，该函数以表格和模式名称作为参数并返回你想要的任何内容：

CREATE OR REPLACE FUNCTION f_tbl_value(_tbl text, _schema text = 'public')
  RETURNS TABLE (value text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _t regclass := to_regclass(_schema || '.' || _tbl);
BEGIN
   IF _t IS NULL THEN
      value := ''; RETURN NEXT;    -- return single empty string
   ELSE
      RETURN QUERY EXECUTE
      'SELECT value FROM ' || _t;  -- return set of values
   END IF;
END
$func$;

呼叫：

SELECT * FROM f_tbl_value('my_table');

或者：

SELECT * FROM f_tbl_value('my_table', 'my_schema');

假设您想要一组带有单个text列或空字符串（如果表不存在）的行。

还假设如果给定的表存在，则存在一个名为value的列。您也可以测试它，但您没有要求。

如果双引号括起来，两个输入参数都区分大小写。就像在SQL语句中处理标识符一样。

在我的示例中，模式名称默认为'public'。根据需要进行调整。您甚至可以完全忽略模式并将其默认为当前search_path。 to_regclass()在Postgres9.4中是新功能。对于旧版本，请替换：

IF EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = _schema
   AND    table_name = _tbl
   ) THEN ...

这实际上更准确，因为它测试了您所需的内容。更多选项和详细说明：

• 将表名作为PostgreSQL函数参数

使用动态SQL时，始终防止SQL注入攻击！在此处进行regclass转换即可解决问题。更多细节：

• 如何检查给定模式中是否存在表

- Erwin Brandstetter

---

谢谢您的全面回答，但我询问的是异常处理而不是检查表是否存在，因为我正在面临竞争条件（不仅在最终不存在表的情况下）。我在官方的psql文档中看到了模拟UPSERT的异常处理示例：http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE，这就是我要求一般优雅的异常处理的原因。 - Tregoreg

最终我在DO块内使用了BEGIN .. END来处理我的INSERT语句，这样可以避免竞态条件的发生，并且我实际上不需要进行任何选择操作。我进行了一些基准测试，似乎以这种方式处理异常只需要大约1毫秒的时间，这对我来说完全令人满意。@ErwinBrandsetter感谢您清晰的解释并强调纯SQL和plpgsql之间的区别，这正是我所缺少的。 - Tregoreg

@ErwinBrandstetter 我尝试了几次。我甚至向互联网上的朋友们求助。似乎无法创建 f_tbl_value 函数。ERROR: syntax error at end of input LINE 13: $func$ LANGUAGE plpgsql; 我不知道为什么。 - jian

@JianHe：我修复了语法错误。 - Erwin Brandstetter

@Tom：使用带有“EXCEPTION”子句的块会更加昂贵。我已经更新了手册中的引用。 - Erwin Brandstetter

Answer 2

如果您只选择一列，则 COALESCE() 函数应该能够为您完成任务。

SELECT COALESCE( value, '{}'::text[] ) FROM myschema.mytable

如果您需要更多的行，您可能需要创建一个带有类型的函数。

- Lucas

---

实际上，我的例子可能有点误导人。我指的是更一般的情况，其中表格实际上可能会丢失（从而引发异常）。我所询问的是异常处理。 - Tregoreg