> The best option is to use exceptions to communicate to the application > what went wrong and then allow the application to handle those > exceptions in many cases. In other cases, the application may need to > know which inputs are mandatory. So other than sanitizing input and making sure it's an appropriate data type, are you relying solely on the database for all your input validation? I guess a good generic example might be setting up a new user account, which might require a username, password, real name, and birth date. We might also put a reasonable constraint that, at the very least, the birth date cannot be greater than the current date. Now if the user submitted a blank page, ideally the page would come back with four errors, one for each field that was left blank. If you submitted this basically as-is to the database, it'd arbitrarily fail on the first column that didn't meet the NOT NULL constraint, and that would be the only error sent back to the client. So yes, this would work, but in theory it could take four or five times before every error was identified and the user notified. > > In another email I did provide a simple example of how I might implement > > this; I don't know if you saw it. Of course, that assumes your database > > isn't designed by .... :-) > > I didn't see it even looking back (I saw the initial attempt and the > PHP sample). Here's a link to the archived message: http://archives.postgresql.org/pgsql-general/2011-07/msg00631.php Feel free to comment/praise/criticize! :-) > SELECT create_transaction(1, current_date, 'Transaction 1', '{(1, 50), > (2, -30), (3, -20)}'); as well since we are now talking about arrays of > records. > > But this is a pain to code to/from SQL in a robust way. Good db > drivers sometimes handle this automatically though. Yes, I've coded exactly this with a plpgsql function and have been mostly pleased by how easy it is. Unfortunately, at least with PHP, it's not so straight forward to format user input into an SQL statement that uses arrays and composite types. It's even worse going the other way -- just Google how to convert SQL arrays into PHP arrays. :-( Karl Nack Futurity, Inc 5121 N Ravenswood Ave Chicago, IL 60640 773-506-2007 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
