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
<polobo@yahoo.com>> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@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.