From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Cc: | sfpug(at)postgresql(dot)org |
Subject: | Re: Best practices? |
Date: | 2003-10-01 05:38:48 |
Message-ID: | 200309302238.48856.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
Dave,
> At the end of any transaction involving foo (including INSERT), a foo
> must have between m and n bars.
>
> Is there a standard way to approach this?
Not a good way, no. What you need is a constraint, or a BEFORE trigger, that
only fires once at the end of each transaction. Even with the statement
triggers coming in 7.4, we don't have that.
Using VIEWs backed by RULEs won't work either, becuase if N is 2 and M is 5,
you need to insert at least 3 bars for each foo in any statement, an
impossibility in SQL.
(FWIW, such constraints are one of the major failings of the SQL standard ...
they are easy to implement in relational calculus, but can only be done
procedurally in SQL)
Unfortunately, I think you're facing using "data-push" functions, where
instead of "insert into foo; insert into bar" you call fn_insert_foo ( foo1,
foo2, {barA1, barA2}, {barB1, barB2});
--
Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-10-01 05:40:19 | Desperately seeking office space |
Previous Message | David Fetter | 2003-09-30 19:13:00 | Best practices? |