Re: Custom Constraint Violation Errors

Hi,

I'm looking for a way to create a custom, for our application parsable,
error message on constraint violation.
The perfect thing would be, having table names, schemas and primary keys
in that error message.

My first thought was to create a function which gets triggered by the
constraint violation, which creates the custom error. But I have not
found any information if this is possible to create a trigger on a
constraint violation, and if yes, how could that be done?

I would be glad, If someone could light me up on that issue.

Thanks in advance and Kind Regards,
Michael
--
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

On Thu, Nov 3, 2011 at 11:56 PM, Michael Musenbrock <redeamer(at)gmx(dot)net> wrote:

> But I have not
> found any information if this is possible to create a trigger on a
> constraint violation, and if yes, how could that be done?

You want to use the special type of "CONSTRAINT" trigger.

http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html

Notice that constraint triggers require the developer of the trigger
to RAISE EXCEPTION when the constraint is violated.

http://www.postgresql.org/docs/9.1/interactive/plpgsql-errors-and-messages.html

The syntax for RAISE EXCEPTION allow the developer to specify any
desired message.

--
Regards,
Richard Broersma Jr.

Am 2011-11-07 16:06, schrieb Richard Broersma:
> On Thu, Nov 3, 2011 at 11:56 PM, Michael Musenbrock <redeamer(at)gmx(dot)net> wrote:
>> But I have not
>> found any information if this is possible to create a trigger on a
>> constraint violation, and if yes, how could that be done?
> You want to use the special type of "CONSTRAINT" trigger.
>
> http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html

Ah, thank you very much, I was already reading about the constraint
trigger, but I
was intentionally looking for a trigger firing on violating of an
already existing constraint.

So am I got that right, that If I want to have eg custom error messages
for a foreign
key violation, I need to replace all fkeys by trigger functions?!

Thanks in advance,
Regards,
Michael

On Mon, Nov 7, 2011 at 2:47 PM, Michael Musenbrock <redeamer(at)gmx(dot)net> wrote:

> was intentionally looking for a trigger firing on violating of an
> already existing constraint.
>
> So am I got that right, that If I want to have eg custom error messages
> for a foreign
> key violation, I need to replace all fkeys by trigger functions?!

I see. The short answer is, while it is possible to custom make your
own constraint triggers that emulate foreign keys, don't do it. This
would be a maintenance nightmare.

I'd be more maintainable to catch these errors in your client
application. Here you would reword these error messages according the
business rules of your client application.

If you insist that the server rewrite your error messages, then you'll
need to create stored functions that will preform the desired
operations; catch any possible errors; and then throw the altered
error messages. But remember that catching errors and throwing them
will have a performance penalty.

http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

--
Regards,
Richard Broersma Jr.

On 11/08/2011 07:06 AM, Richard Broersma wrote:

> I'd be more maintainable to catch these errors in your client
> application. Here you would reword these error messages according the
> business rules of your client application.

+1

It's not hard to create:

CONSTRAINT some_constraint_name FOREIGN KEY col REFERENCES blah(id)

... then in the app, match "some_constraint_name" and map it to a
suitable error. That's what I do and it works very well for all
constraint types, not just foreign key constraints.

--
Craig Ringer