处理PostgreSQL异常的优雅方法?

编程入门行业动态更新时间:2024-10-24 09:19:47

本文介绍了处理PostgreSQL异常的优雅方法?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

在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中捕获错误(处理异常)

示例: SELECT或INSERT函数是否容易出现竞争状况?

在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'.适应您的需求.您甚至可以完全忽略该模式，并默认使用当前的 search_path .
  
  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:
  - 如何检查如果表存在于给定的模式中