Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

Function to raise an error (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 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$  LANGUAGE plpgsql STRICT;

COMMENT ON FUNCTION f_raise(text, text) IS 'Raise error.
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 AS
$func$
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
$func$  LANGUAGE plpgsql;

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');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.