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

I could not find a direct way to output the the "CONTEXT" line with a user-defined exception. This option is just not implemented (yet) in PostgreSQL 9.1. Read the manual here.
However, I found a ...

Workaround

... that should perform flawlessly. You can make plpgsql behave as desired by calling another function that raises the error for you. This works with PostgreSQL 9.0 or later.
_{For version 8.4 you have to make a minor adjustment: Parameters cannot be assigned to. I annotated the code.}

Function to raise an error (or warning, notice, ..) with a user defined message and CONTEXT:

CREATE OR REPLACE FUNCTION f_raise(_lvl text = 'EXCEPTION'
                                  ,_msg text = 'Default error msg.')
  RETURNS void LANGUAGE plpgsql STRICT AS
$BODY$
BEGIN
   -- Optional default message. v8.4: declare _msg as var and assign $2 to it.
   IF _msg IS NULL THEN
       _msg := 'Default error msg.';
   END IF;

   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;
$BODY$;

COMMENT ON FUNCTION f_raise(text, text) IS 'Raise error.
Call from inside another function instead of raising an error directly
  to trick plpgsql into adding 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
$BODY$
BEGIN
   -- do stuff    

   IF TRUE THEN
      -- instead of raising error like this:
      -- RAISE EXCEPTION 'unexpected parameter: "%"', $1;
      PERFORM f_raise('EXCEPTION', 'My message "' || $1 || '"');
   END IF;
END;
$BODY$;       

Demo call:

SELECT test_err('wrong parameter');

Default values and named parameters (update)

I improved the syntax and added default values to the function definition. If you call it without parameters (or just one) and the defaults will be used for missing values. 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');