Обсуждение: Custom Contraint 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@gmx.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@gmx.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@gmx.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