Benjamin Smith wrote:
We have a list of customers, some of whom have purchased feature X and some of whom have not. If a customer has paid for featurex, they can use it, and a strict relationship between cust_items.items_id and items.id, but only if they are signed up to use featurex, otherwise I want cust_items.items_id to be NULL.
Currently, I have tables defined similar to:
create table Customer (
id serial unique not null, name varchar(30) unique not null, FeatureX bool not null
);
Create table cust_items (
id serial unique not null, customer_id integer not null references customer(id), name varchar(30) not null, type varchar not null, items_id integer default null references featurex(id), cust_active bool not null );
// type is one of "book", "tape", or "featurex"
Create table items (
id serial not null unique, title varchar(30)
);
I want to say "If the cust_items.type='featurex' then (
(customer.featurex must be true) AND (cust_items.items_id must be in (select id from items) )";
I'm just stumped as to how to say this.
I've tried, with the above table defs, CREATE RULE check_customer ON UPDATE to cust_items WHERE NEW.type='featurex' AND NEW.customer_id IN (SELECT customer.id FROM customer WHERE featurex=TRUE
)
DO ... ? <too many tries to count>
Any pointers, hints, or info on this kind of statement?
This is a trigger job not a rule one.
Regards Gaetano Mendola
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
