I think the best way is what David has suggested...
But if it is already live, and there is no way to handle clients app to work with functions (instead of Direct SQL statements) then I think trigger function would help... (not sure how it could be error prone..) So basically if function is Validate(input parameters) Insert/Update data Inside trigger function call the same check Validation function(s) and then if it returns false - return NULL, otherwise return NEW Kind Regards, Misa 2011/5/5 David Johnston <pol...@yahoo.com> > > -----Original Message----- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Jack Christensen > > Sent: Thursday, May 05, 2011 3:20 PM > > To: pgsql > > Subject: [GENERAL] Multiple table relationship constraints > > > > 4. Validate application side -- this can work well, but it leaves the > hole > of a > > bug in the application or a direct SQL statement going bad. > > > > > > Anyone have any advice on the best way to handle this? > > > > Not totally following the usage though I have come across similar > requirements before. A variant of #4 would be to remove > INSERT/UPDATE/DELETE permissions on the relevant tables and write SECURITY > DEFINER functions to perform those actions instead. You can additionally > leave the constraints loose and have the function query the tables > post-modification to make sure they are still valid (kind of like the > materialized view option but without a permanent table). > > David J. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
