May 14th, 2008 by depesz | Tags: , , , | 4 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

Today we have one new feature – extension of plpgsql's RAISE command.

Patch was written by Pavel Stehule and Tom Lane, and committed by Tom.

Commit log:

Improve plpgsql's RAISE command. It is now possible to attach DETAIL and
HINT fields to a user-thrown error message, and to specify the SQLSTATE
error code to use. The syntax has also been tweaked so that the
Oracle-compatible case "RAISE exception_name" works (though you won't get a
very nice error message if you just write that much). Lastly, support
the Oracle-compatible syntax "RAISE" with no parameters to re-throw
the current error from within an EXCEPTION block.
In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists,
so that there is a way to trap errors with custom SQLSTATE codes.
Pavel Stehule and Tom Lane

Well, up until now RAISE was limited to very basic things like:

RAISE NOTICE 'DEBUG: Variable x = %', x;
RAISE EXCEPTION 'User with given email (%) already exist.', in_email;

Of course you could use another log levels as well, but that was pretty much all of functionality.

Now, with this new patch there are much more options.

First of all, we can send “HINT" message together with error:

# CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
DECLARE
BEGIN
IF i < 15 THEN
raise exception 'Too small value of i = %', i USING HINT = 'Try using value of 15 or higher.';
END IF;
RETURN;
END;
$BODY$ language plpgsql;
# SELECT test_raise(2);
ERROR: Too small value of i = 2
HINT: Try using value of 15 or higher.

Nice. We can make it even better:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
DECLARE
BEGIN
IF i < 15 THEN
raise exception 'Too small value of i.' USING HINT = 'Try using value of 15 or higher.', DETAIL = 'Given i = ' || i;
END IF;
RETURN;
END;
$BODY$ language plpgsql;
# SELECT test_raise(2);
ERROR: Too small value of i.
DETAIL: Given i = 2
HINT: Try using value of 15 or higher.

This is of course pretty useful, but what I like the most from this new patch is:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
DECLARE
BEGIN
IF i < 15 THEN
raise exception 'Too small value of i.' USING ERRCODE = 'unique_violation';
END IF;
RETURN;
END;
$BODY$ language plpgsql;

When I run it, nothing really cool happens:

# SELECT test_raise(2);
ERROR: Too small value of i.

So, what is this ERRCODE?

This can be used in error trapping with BEGIN EXCEPTION END blocks to distinguish between various errors!

For example. Let's change our function to:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
BEGIN
IF i < 15 THEN
raise exception 'Bad i.' USING ERRCODE = 'unique_violation';
END IF;
IF i > 1000000 THEN
raise exception 'Bad i.' USING ERRCODE = 'program_limit_exceeded';
END IF;
RETURN;
END;
$BODY$ language plpgsql;
CREATE OR REPLACE FUNCTION call_test(i INT4) RETURNS void as $BODY$
BEGIN
BEGIN
perform test_raise(i);
EXCEPTION
WHEN unique_violation THEN
raise notice 'It looks like we have unique violation.';
WHEN program_limit_exceeded THEN
raise notice 'It looks like we have program LIMIT exceeded.';
END;
RETURN;
END;
$BODY$ language plpgsql;

# SELECT call_test(2);
NOTICE: It looks like we have unique violation.
call_test
-----------
 
(1 row)
# SELECT call_test(20000000);
NOTICE: It looks like we have program LIMIT exceeded.
call_test
-----------
 
(1 row)
# SELECT call_test(20);
call_test
-----------
 
(1 row)

As you can see, now the external function can easily check what happened. Before this, all “raise exceptions" raised “raise_exception" error, which was not really useful.

ERRCODE can take codename of number of error. List of all possible errors is available in PostgreSQL docs.

Interesting part is, that while you can't create your own textual errcodes, you can easily return unknown “numerical" codes:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
BEGIN
IF i < 15 THEN
raise exception 'Bad i.' USING ERRCODE = 'P0004';
END IF;
IF i > 1000000 THEN
raise exception 'Bad i.' USING ERRCODE = 'program_limit_exceeded';
END IF;
RETURN;
END;
$BODY$ language plpgsql;

In this case, calling external function will work like this:

# SELECT call_test(2);
ERROR: Bad i.
CONTEXT: SQL statement "SELECT test_raise( $1 )"
PL/pgSQL function "call_test" line 3 at PERFORM

And lastly, this patch allows You to simplify exception throwing, by allowing syntax like this:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
BEGIN
raise unique_violation;
RETURN;
END;
$BODY$ language plpgsql;
# SELECT test_raise(2);
ERROR: unique_violation

This raised exception with errcode ‘unique_violation', and since there was no message, errcode was used instead. To add a message, you can use method describe above, or:

CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void as $BODY$
BEGIN
RAISE unique_violation USING MESSAGE = 'test message';
RETURN;
END;
$BODY$ language plpgsql;
# SELECT test_raise(2);
ERROR: test message

Happy hacking :)

  1. 4 comments

  2. # daniel
    May 15, 2008

    “Patch was written by Pavel Stehule and Tom Land, and committed by Tom.” should be read “(…) and Tom Lane, (…)”

  3. May 15, 2008

    @daniel: thanks, fixed.

  4. # Craig Ringer
    May 19, 2008

    Of all the wonderful things coming in 8.4 this has to be the best so far.

    It’s the first feature that’s made me check out cvs and upgrade my development database on the spot. The best thing is that the protocol already supports these features for normal server error messages so the JDBC driver doesn’t even need to catch up. Stored-proc heavy database use just got a whole lot nicer.

    Every Pg release knocks a few items off my private wishlist, but this probably second only to automatic cached query plan invalidation on the “awesome” scale.

    I guess I’d better start dreaming about being able to use INSERT/UPDATE/DELETE RETURNING in subqueries or as input to INSERT / UPDATE … FROM . At this rate some kind (brilliant) soul will then contribute them to 8.5 .

  1. 1 Trackback(s)

  2. Apr 20, 2012: PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant

Leave a comment