Generate an exception with a Context

Question

When PostgreSQL throws an exception, there is a line "CONTEXT" like:

 ERROR: INSERT has more target COLUMNS than expressions
 LINE 3: ...
 ^
 QUERY: INSERT INTO ...
 CONTEXT: PL/pgSQL FUNCTION "XXXXX" line 4 at SQL statement

But when I throw an exception, this line is not there. I didn't find how to add it.

 RAISE EXCEPTION 'blablabla' USING HINT = 'blablablabla';

Is it possible to add this line to my exception?

Answer 1

There is no direct way to output the the CONTEXT line with a user-defined exception. This option is not implemented (yet) in PostgreSQL 9.1.
However, I found a ...

Workaround

... that should perform flawlessly. Call another function that raises the error (warning, notice, ..) for you. Optionally with custom message and CONTEXT. Works with PostgreSQL 9.0 or later:

CREATE OR REPLACE FUNCTION f_raise(_lvl text = 'EXCEPTION'
 , _msg text = 'Default error msg.')
 RETURNS void
 LANGUAGE plpgsql AS
$func$
BEGIN
 CASE upper(_lvl)
 WHEN 'EXCEPTION' THEN RAISE EXCEPTION '%', _msg;
 WHEN 'WARNING' THEN RAISE WARNING '%', _msg;
 WHEN 'NOTICE' THEN RAISE NOTICE '%', _msg;
 WHEN 'DEBUG' THEN RAISE DEBUG '%', _msg;
 WHEN 'LOG' THEN RAISE LOG '%', _msg;
 WHEN 'INFO' THEN RAISE INFO '%', _msg;
 ELSE RAISE EXCEPTION 'f_raise(): unexpected raise-level: "%"', _lvl;
 END CASE;
END
$func$;
COMMENT ON FUNCTION f_raise(text, text) IS 'Raise error or given level with msg and context.
Call from inside another function instead of raising an error directly
 to get plpgsql to add CONTEXT (with line number) to error message.
1ドル .. error level: EXCEPTION | WARNING | NOTICE | DEBUG | LOG | INFO
2ドル .. error message';

Use the function to raise an error like this:

CREATE OR REPLACE FUNCTION test_err(text)
 RETURNS void
 LANGUAGE plpgsql AS
$func$
BEGIN
 -- do stuff 
 IF TRUE THEN -- some condition here?
 -- instead of raising error like this:
 -- RAISE EXCEPTION 'unexpected parameter: "%"', 1ドル;
 PERFORM f_raise('EXCEPTION', 'My message "' || 1ドル || '"');
 END IF;
END
$func$;

Call:

SELECT test_err('wrong parameter');

The function comes with default values, that kick in when missing in the call. In combination with named parameters, you can do pretty much anything. Examples:

SELECT f_raise();
SELECT f_raise('WARNING');
SELECT f_raise(_msg := 'boohoo');
SELECT f_raise(_lvl := 'WARNING');