Re: Multiple table relationship constraints

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(at)yahoo(dot)com>

> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> > owner(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>