I'm only moderately familiar with postgres and the DBI related modules that I'm using to access it so forgive me if this is a simple question. I've been looking for several hours for information on this and have not found a satisfying answer.
I'm writing a simple CGI application to do simple create, update, delete operations on a few tables and although doing these basic operations is trivial I am having a great deal of trouble figuring out the best way to validate data being inserted and handle related errors.
My question in a nutshell is when performing an INSERT, how do I determine exactly which columns are responsible for the error when one occurs(so that within the application I can say "hey user check how you typed field x, field y). The SQLSTATUS error codes are very non-specific and I cannot find another method for checking what failed without parsing text output(unreasonable for many reasons). Perhaps there is a way to define an INSERT trigger that could place/return the names of the problem columns somewhere? Doing this in a way that does not tie one only to PGSQL(although it's my favorite) is even more of a problem.
It seems to me that all information about acceptable values, constraints and types for columns is already present within the table definitions so duplicating those checks and definitions within the application and user view is folly.
