Re: Calculated fileds in pg

Oops, forgot to post the schemas:

create table INVOICES (

id serial primary key,
member_id integer references MEMBERS(id),
submit_time timestamp (0) without time zone default
current_timestamp(0),
customer_charged boolean not null default false,
order_filled boolean not null default false,
double_checked boolean not null default false,
cancelled boolean not null default false,
cancel_time timestamp (0) ,
payment_returned boolean default false,
payment_type integer references payment_types(id),
member_comment text ,
addressee text ,
planned_pay_date date ,
payment_rcvd_date date ,
shipping_addr text not null,
contents_dsc text ,
req_del_date1 date ,
req_del_date2 date ,
req_del_date3 date ,
req_del_time1 integer references DEL_TIMES(id),
req_del_time2 integer references DEL_TIMES(id),
req_del_time3 integer references DEL_TIMES(id),
escalation_level int2 not null default 0,
cc_name text ,
cc_number text ,
cc_exp_date date ,
cc_holder_name text ,
payment_rcvd boolean default false,
total_price integer , -- this is the sum(price) of
matching columns in invoice_li
shipping_fee integer ,
delivery_method integer references DEL_METHODS(id),
admin_shipping_memo integer references ADMIN_SHIPPING_MEMOS(id)
);

AND

create table INVOICE_LI (

invoice_id integer references INVOICES(id) on
delete cascade,
prod_id text references PRODUCTS(id),
dist_id integer references distributors(id),
shop_id integer references CHARISMA_SHOPS(id)
default NULL,
quantity int2 not null,
old_qty int2 default 0,
price integer not null,
shipped boolean not null default false,
date_shipped timestamp (0) ,
dist_invoice integer references DIST_ORDERS(id)
default NULL,
item_status integer references DIST_ITEM_STATUS,
received boolean default false,
ship_now boolean default false,
exp_rcvd_date timestamp (0) ,
cancelled boolean default false,
cancel_time timestamp (0) ,
credited integer default 0,
need_to_credit integer default 0,

primary key (invoice_id, prod_id)
);