Postgresql Rules - Mailing list pgsql-sql

From Staten Oliver
Subject Postgresql Rules
Date
Msg-id 1237578858.6665.34.camel@lapdance.me-equip.com
Whole thread Raw
List pgsql-sql
I have three tables (CUST_CNTCTS, CUST_CO_BILL_ADDR,  and CUST_CNTCT_MAILING_LIST_ADDR) using postgresql 8.1.10<br
/><br/> CUST_CNTCTS contains contacts for companies we do business with<br /><br /> CUST_CO_BILL_ADDR contains billing
addressfor these companies<br /><br /> CUST_CNTCT_MAILING_LIST_ADDR associates contacts with addresses so we know where
tosend marketing information for each contact <br /><br /> I have a rule (that works correctly) in CUST_CNTCTS that
removesa contact from CUST_CNTCT_MAILING_LIST_ADDR when their "mailing_list" flag is turned from true to false <pre>
 
CREATE OR REPLACE RULE "mailing_list_to_malng-lst" 
AS ON UPDATE TO meedb."CUST_CNTCTS"
WHERE old.mailing_list <> new.mailing_list AND new.mailing_list = false 
DO  DELETE FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE new.cust_cntct_id = "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id;
COMMENT ON RULE "mailing_list_to_malng-lst" ON meedb."CUST_CNTCTS" IS 'If mailing_list is changed from true to false,
removethe contact from the mailing list';
 
</pre> There is also a rule in CUST_CO_BILL_ADDR that sets CUST_CNTCTS."mailing_list" to false when the address for a
contact(that is in CUST_CNTCT_MAILING_LIST_ADDR) is deactivated <pre>
 
CREATE OR REPLACE RULE "status_to_malng-lst" AS
ON UPDATE TO meedb."CUST_CO_BILL_ADDR"
WHERE old.status::text <> new.status::text AND new.status::text = 'i'::text 
DO  UPDATE meedb."CUST_CNTCTS" SET mailing_list = false
WHERE ("CUST_CNTCTS".cust_cntct_id IN 
    (SELECT "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id
     FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
     WHERE "CUST_CNTCT_MAILING_LIST_ADDR".cust_bill_addr_id = new.cust_bill_addr_id));
COMMENT ON RULE "status_to_malng-lst" ON meedb."CUST_CO_BILL_ADDR" IS 'If the status of a company''s billing address is
changedfrom active to inactive, remove the contacts associated with this address from the mailing list by setting the
mailing_listflag in CUST_CNTCTS to FALSE';
 
</pre> The problem is that when I change the status of the address from a (active) to i (inactive) the contacts
associatedwith the address are removed from CUST_CNTCT_MAILING_LIST_ADDR, but CUST_CNTCTS."mailing_list" is not set to
FALSE<br/><br /> Does anyone see any problems with what I am doing, or is this something that can't be done with
postgresql'srule system? <pre>
 

</pre>

pgsql-sql by date:

Previous
From: "Kevin Duffy"
Date:
Subject: simple SQL question
Next
From: "Kevin Duffy"
Date:
Subject: Re: simple SQL question