处理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;

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

2 个解决方案

#1

Exception handling in PL/pgSQL

Generally, plpgsql code is always wrapped into a BEGIN .. END block. That can be inside the body of a DO statement 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 BEGIN block can optionally include an EXCEPTION clause 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

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

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

  示例:SELECT或INSERT是否容易出现竞争条件?

• Search for related answers on SO

  在SO上搜索相关答案

How to avoid an exception in the example

A DO statement can't return anything. Create a function that 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 text column or an empty string if the table does not exist.

  假设您想要一组具有单个文本列的行或如果该表不存在则需要空字符串。

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

  还假设如果给定的表存在,则存在列值。你也可以测试一下,但你没有要求。

• Both parameters are case sensitive text values. 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.

  两个参数都是区分大小写的文本值。这与SQL语句中的标识符处理方式略有不同。如果你从不重复引用标识符,请传递小写名称,你没事。

• 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.

  在我的示例中,架构名称默认为“public”。适应您的需求。您甚至可以完全忽略该架构,并默认为当前的search_path。

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

  to_regclass()是Postgres 9.4中的新功能。对于旧版本替代:

  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

  表名作为PostgreSQL函数参数

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

  使用动态SQL时始终防止SQL注入!对于regclass的强制转换就是这里的诀窍。更多细节:

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

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

#2

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.

如果需要更多行,则可能需要创建具有类型的函数。