Postgres: Returning Results or Error from Stored Functions

Question

I am struggling to figure out how to best handle the return of results or errors to my application from Postgres stored functions.

Consider the following contrived psudeocode example:

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

The function can fail with a specific error or succeed and return 0 or more resources.

At first I tried creating a custom type to always use as a standard return type in eachh function:

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

Postgres does not allow this however:

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

I then discovered OUT parameters and attempted the following uses:

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 doesn't like this either:

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

Also tried a similar function but reversed: Returning a custom app.appresult object and setting the OUT param to "SETOF RECORD". This was also not allowed.

Lastly i looked into Postgres exception handling using

RAISE EXCEPTION 'ERR_MY_ERROR';

So in the example function, i'd just raise this error and return. This resulted in the driver sending back the error as:

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

This is easy enough to parse but doing things this way feels wrong.

What is the best way to solve this problem? Is it possible to have a custom AppResult object that i could return?

Something like:

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

//Edit 1 //

I think I'm leaning more towards @Laurenz Albe solution.

My main goal is simple: Call a stored procedure which can return either an error or some data.

Using RAISE seems to accomplish this and the C++ driver allows easy checking for an error condition returned from a query.

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

I'm also wondering about using custom SQLSTATE codes instead of parsing the driver string.

Throwing '__404' might mean that during the course of my SPs execution, it could not continue because some record needed was not found.

When calling the sql function from my app, i have a general idea of what it failing with a '__404' would mean and how to handle it. This avoids the additional step of parsing driver error string.

I can also see the potential of this being a bad idea.

Bedtime reading: https://www.postgresql.org/docs/current/static/errcodes-appendix.html

Amon

Raising an exception in case of an error is the proper solution – Resor

Answer 1

This is slightly opinion based, but I think that throwing an error is the best and most elegant solution. That is what errors are for!

To distinguish various error messages, you could use SQLSTATEs that start with 6, 8 or 9 (these are not used), then you don't have to depend on the wording of the error message.

You can raise such an error with

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

Sleep

Answer 2

We do something similar to what you're trying to do, but we use TEXT rather than ANYELEMENT, because (almost?) any type can be cast to TEXT and back. So our type looks something like:

(errors our_error_type[], result TEXT)

The function which returns this stores errors in the errors array (it's just some custom error type), and can store the result (cast to text) in the result field.

The calling function knows what type it expects, so it can first check the errors array to see if any errors were returned, and if not it can cast the result value to the expected return type.

As a general observation, I think exceptions are more elegant (possibly because I come from a c# background). The only problem is in plpgsql exception handling is (relatively) slow, so it depends on the context - if you're running something many times in a loop, I would prefer a solution that doesn't use exception handling; if it's a single call, and/or especially when you want it to abort, I prefer raising an exception. In practice we use both at various points throughout our call stacks.

And as Laurenz Albe pointed out, you're not meant to "parse" exceptions, so much as raise an exception with specific values in specific fields, which the function that catches the exception can then extract and act on directly.

---

As an example:

Setup:

CREATE TABLE my_table (id INTEGER, txt TEXT);
INSERT INTO my_table VALUES (1,'blah');

CREATE TYPE my_type AS (result TEXT);

CREATE OR REPLACE FUNCTION my_func()
RETURNS my_type AS
$BODY$
DECLARE
    m my_type;
BEGIN
    SELECT my_table::TEXT
        INTO m.result
    FROM my_table;

    RETURN m;
END
$BODY$
LANGUAGE plpgsql STABLE;

Run:

SELECT (m.result::my_table).*
FROM my_func() AS m

Result:

| id | txt  |
-------------
| 1  | blah |

Ardellardella

I recreated the custom return type as {success bool, error text, result text} and attempted to select rows from a table into it's result field: gist.github.com/Think7/7d30047c73e4c41d78b293a85127dee2 The result is "(f,MY_ERROR,Wendy)" returned by the driver as a string. I need the entire result set returned to the app. – Amon

@Amon If you want it to return a single row, do: SELECT resources::TEXT FROM app.resources INTO vReturn.result;. This selects the whole record in a non-expanded state (not a technical term). Then the calling code can cast it back and expand it if it wants, to access individual fields. However if you want to return multiple rows, this won't work (unless you aggregate them, but then it becomes silly) and you're better off using exceptions. – Ardellardella

@Amon See the edit for an example of how it works. – Ardellardella