On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote: > I need to add some complex constraints at the DB. > > For example. > > Do not allow a line item of inventory to be changed if it does not > result in the same number of joints originally shipped. > > These will involve several tables. > > What is the best approach for this? Triggers. > Here is what I have been trying. > > CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint) > RETURNS double precision AS > 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi > left join view.generic_item_shipment_id v on v.id=gi.id > left join v_shipment_jts_off vsjo on vsjo."shipmentId"=v.shipment_id > where gi.id=$1;' > LANGUAGE 'sql' VOLATILE > COST 100; > ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco; > > alter table inventory.t_generic_item add constraint > check_shipment_original_jts CHECK (numoriginaljts(id)=0); > > *Does this approach seem reasonable? Nope. You're lying to the database by wrapping otherwise disallowed SQL in a check constraint, and it will get its revenge. > This did not work, but it is probably my error. It actually let me > break the constraint, but my constraint kicked in when I tried to > correct the problem. Can someone point me to an example of doing > something like this?* > > The point of this is to never let the total number of original > pieces be different than the number originally shipped. > > My code has done this occasionally and users can override the > inventory. > > Basically I would rather the application throw an error than let > this number become unbalanced. You might want to talk to people who have done bookkeeping applications for PostgreSQL, or possibly even buy one of the proprietary PostgreSQL-based systems for it, as this stuff can be fiendishly tricky to get right. Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
