How to access error message text in plpgsql

From: Leon Starr <leon_starr(at)modelint(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How to access error message text in plpgsql
Date: 2010-08-09 18:16:00
Message-ID: B345551B-90BB-4900-99F8-1452063A6225@modelint.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I am writing plpgsql functions that will interface with a java front end. For portability reasons I would like the API I am
presenting to be as non-databasey as possible, this includes error reporting. I am wondering if there is a way that I can
access/parse the error text when an exception is raised so that I can create my own non-databasey, but informative
messages through my API.

Example message:
contracts=# select UI_new_integer_data_type('Rating2', 'Stars', 5, 0, 6);
ERROR: new row for relation "integer_data_type" violates check constraint "default_outofrange"

I would like to catch this (I know how to do that part!) and raise my own exception along the lines of:
raise exception "Default value [%] must be between the min [%] and max [%] values.", default_value, min, max;
(more user friendly and db terminology such as 'row' and 'constraint' is eliminated)

Now I could just do:
exception check_violation
but there are multiple check constraints on the same table, but I did name each of them.

So it seems like I ought to be able to access the message text, parse out the name, "default_outofrange" in this case
and use that to distinguish it from a different constraint on the same table.

Question 1) Is it possible to access the error message text and if so how?
2) If it is not possible, the only other alternative seems to be to
- create a db wrapper layer on the java side to refrabulate the message
- break out the db interactions into sufficiently small operations so that I can precisely tell one error from another (ugh)
right?

Thanks for reading this far! Any help appreciated.

- Leon

Browse pgsql-novice by date

  From Date Subject
Next Message Bruce Momjian 2010-08-09 22:53:04 Re: How to remove quotes from COPY TO result?
Previous Message Thom Brown 2010-08-09 16:29:02 Re: Trying to understand why a null "fails" a select