Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group