Re: Calculated fileds in pg - Mailing list pgsql-general

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)
);