SQL 处理 PostgreSQL 异常的优雅方式？

Elegant way of handling PostgreSQL exceptions?

提问by Tregoreg

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;

I could do the safe-wrapping in the client application:

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

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

But could I do such a thing in SQL directly? I would like to make the following code work, but it seems like it should by put into DO $$ ... $$block and here I'm getting lost.

但是我可以直接在 SQL 中做这样的事情吗？我想让下面的代码工作，但似乎它应该放入DO $$ ... $$块中，在这里我迷路了。

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

采纳答案by Erwin Brandstetter

Exception handling in PL/pgSQL

PL/pgSQL 中的异常处理

Generally, plpgsql code is always wrapped into a BEGIN .. ENDblock. That can be inside the body of a DOstatement or a function. Blocks can be nested inside - but they cannot exist outside, don't confuse this with plain SQL.

通常，plpgsql 代码总是被包装成一个BEGIN .. END块。这可以在DO语句或函数的主体内。块可以嵌套在内部 - 但它们不能存在于外部，不要将其与普通 SQL 混淆。

Each BEGINblock can optionally include an EXCEPTIONclause for handling exceptions, but functions that need to trap exceptions are considerably more expensive, so it's best to avoid exceptions a priori.

每个BEGIN块都可以选择包含一个EXCEPTION用于处理异常的子句，但是需要捕获异常的函数的开销要大得多，因此最好事先避免异常。

More information:

更多信息：

• The manual on how to trap errors (handle exceptions) in PL/pgSQL

• Example: Is SELECT or INSERT in a function prone to race conditions?

• Search for related answers on SO

• 关于如何在 PL/pgSQL 中捕获错误（处理异常）的手册

• 示例：函数中的 SELECT 或 INSERT 是否容易出现竞争条件？

• 在 SO 上搜索相关答案

How to avoidan exception in the example

如何避免示例中的异常

A DOstatement can't return anything. Create a functionthat takes table and schema name as parameters and returns whatever you want:

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

CREATE OR REPLACE FUNCTION f_tbl_value(_tbl text, _schema text = 'public')
  RETURNS TABLE (value text) 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
$func$ LANGUAGE plpgsql;

Call:

称呼：

SELECT * FROM f_tbl_value('my_table');

Or:

或者：

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

• Assuming you want a set of rows with a single textcolumn or an empty string if the table does not exist.

• Also assuming that a column valueexists if the given table exists. You could test for that, too, but you didn't ask for that.

• Both parameters are case sensitivetextvalues. That's subtly different from how identifiers in SQL statements are handled. If you never double-quote identifiers, pass lower case names and you are fine.

• The schema name defaults to 'public'in my example. Adapt to your needs. You could even ignore the schema completely and default to the current search_path.

• to_regclass()is new in Postgres 9.4. For older versions substitute:

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

  This is actually more accurate, because it tests exactly what you need. More options and detailed explanation:

  • Table name as a PostgreSQL function parameter

• Always defend against SQL injection when working with dynamic SQL! The cast to regclassdoes the trick here. More details:

  • How to check if a table exists in a given schema

• 假设您想要一组具有单列的行，text或者如果表不存在则为空字符串。

• 还假设value如果给定表存在，则存在一列。你也可以测试那个，但你没有要求那个。

• 这两个参数都是区分大小写的text值。这与处理 SQL 语句中标识符的方式略有不同。如果您从不使用双引号标识符，则传递小写名称就可以了。

• 'public'在我的示例中，模式名称默认为。适应您的需求。您甚至可以完全忽略架构并默认为当前的search_path.

• to_regclass()在 Postgres 9.4 中是新的。对于旧版本替换：

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

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

  • 表名作为 PostgreSQL 函数参数

• 使用动态 SQL 时始终防御 SQL 注入！演员regclass阵容在这里发挥作用。更多细节：

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

回答by Lucas

If you are selecting only one column then the COALESCE() function should be able to do the trick for you

如果您只选择一列，那么 COALESCE() 函数应该能够为您解决问题

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

If you require more rows you may require to create a function with types.

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