处理PostgreSQL异常的优雅方法? [英] Elegant way of handling PostgreSQL exceptions?

问题描述

在PostgreSQL中，我想创建一个安全包装机制，如果发生异常，该机制将返回空结果.请考虑以下内容:

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

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 = []
}

但是我可以直接在SQL中做这样的事情吗?我想使以下代码正常工作，但似乎应该将其放入DO $$ ... $$块中，在这里我迷路了.

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.

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

推荐答案

PL/pgSQL中的异常处理

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

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.

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

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.

更多信息:

• 该手册如何在PL/pgSQL中捕获错误(处理异常)

示例: 在SO上搜索相关答案

DO 语句无法返回任何内容. 创建一个带表的功能 和模式名称作为参数，并返回您想要的任何内容:

A DO statement can't return anything. Create a function that takes table and schema name as parameters and returns whatever you want:

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;

致电:

SELECT * FROM f_tbl_value('my_table');

或者:

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

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

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

    如果给定表存在，还假定列value存在.您也可以对此进行测试，但您并没有要求.

    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.

    两个参数均为区分大小写 text值.这与标识符在SQL语句中的方式稍有不同被处理.如果您从未使用过双引号，请使用小写字母名称就可以了.

    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.

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

    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()是Postgres 9.4 中的新功能.对于旧版本，请替换:

    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:

    • 表名作为PostgreSQL函数参数

    在使用动态SQL时始终防御SQL注入！强制转换为regclass可以解决此问题.详细信息:

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

    • 如何检查如果表存在于给定的模式中

    这篇关于处理PostgreSQL异常的优雅方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！