postgresql - Postgres : Returning Results or Error from Stored Functions

Question

我正在努力弄清楚如何最好地处理从 Postgres 存储函数向我的应用程序返回结果或错误。

考虑以下人为的伪代码示例:

app.get_resource(_username text)
    RETURNS <???>

BEGIN

    IF ([ ..user exists.. ] = FALSE) THEN
        RETURN 'ERR_USER_NOT_FOUND';
    END IF;

    IF ([ ..user has permission.. ] = FALSE) THEN
        RETURN 'ERR_NO_PERMISSION';
    END IF;

    -- Return the full user object.
    RETURN QUERY( SELECT 1 
        FROM app.resources
        WHERE app.resources.owner = _username);

END

该函数可以因特定错误而失败，也可以成功并返回 0 个或更多资源。

起初我尝试创建一个自定义类型以始终用作 eachh 函数中的标准返回类型:

CREATE TYPE app.appresult AS (
  success boolean,
  error   text,
  result  anyelement
);

但是 Postgres 不允许这样做:

[42P16] ERROR: column "result" has pseudo-type anyelement

然后我发现了 OUT 参数并尝试了以下用途:

CREATE OR REPLACE FUNCTION app.get_resource(
    IN      _username   text,
    OUT     _result app.appresult -- Custom type 
                                  -- {success bool, error text}
)

RETURNS SETOF record
AS
$$
BEGIN

  IF 1 = 1 THEN -- just a test
    _result.success = false;
    _result.error   = 'ERROR_ERROR';
    RETURN NULL;
  END IF;

  RETURN QUERY(SELECT * FROM app.resources);

END;
$$
  LANGUAGE 'plpgsql' VOLATILE;

Postgres 也不喜欢这样:

[42P13] ERROR: function result type must be app.appresult because of OUT parameters

还尝试了类似的功能但相反:返回自定义 app.appresult 对象并将 OUT 参数设置为“SETOF RECORD”。这也是不允许的。

最后我研究了 Postgres 异常处理使用

RAISE EXCEPTION 'ERR_MY_ERROR';

所以在示例函数中，我只是提出这个错误并返回。这导致驱动程序发回错误:

"ERROR:  ERR_MY_ERROR\nCONTEXT:  PL/pgSQL function app.test(text) line 6 at RAISE\n(P0001)"

这很容易解析，但以这种方式做事感觉不对。

解决这个问题的最佳方法是什么？是否可以有一个我可以返回的自定义 AppResult 对象？

类似于:

{ success bool, error text, result <whatever type> }

//编辑1//

我认为我更倾向于@Laurenz Albe 解决方案。

我的主要目标很简单:调用一个可以返回错误或一些数据的存储过程。

使用 RAISE 似乎可以实现这一点，并且 C++ 驱动程序允许轻松检查从查询返回的错误条件。

if ([error code returned from the query] == 90100)
{
    // 1. Parse out my overly verbose error from the raw driver
    //    error string.
    // 2. Handle the error.    
}

我也想知道如何使用自定义 SQLSTATE 代码而不是解析驱动程序字符串。

抛出“__404”可能意味着在我的 SP 执行过程中，它无法继续，因为找不到所需的某些记录。

当从我的应用程序调用 sql 函数时，我大致了解它因“__404”而失败意味着什么以及如何处理它。这避免了解析驱动程序错误字符串的额外步骤。

我也能看出这是个坏主意的可能性。

睡前阅读: https://www.postgresql.org/docs/current/static/errcodes-appendix.html

Answer 1

这有点基于意见，但我认为抛出错误是最好和最优雅的解决方案。这就是错误的原因!

为了区分各种错误消息，您可以使用以 6、8 或 9 开头的 SQLSTATE(这些未使用)，那么您就不必依赖于错误消息的措辞。

你可以用

引发这样的错误

RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'my own error';