Skip site navigation (1) Skip section navigation (2)

a rule question

From: Brad Paul <bpaul(at)carolina(dot)rr(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: a rule question
Date: 2002-10-22 17:32:37
Message-ID: 0c03e07331716a2FE6@mail6.carolina.rr.com (view raw or flat)
Thread:
Lists: pgsql-novice
I have two tables one is called "inventory" and the other is 
"inventory_usage". When I insert into inventory_usage info like number of 
widgets used for a given order_number I would the in_stock entry in the 
inventory to be adjusted. Here are the two tables and my current attempts at 
the rule:

create sequence "inventory_id_seq" start 1 increment 1;
create table "inventory" (
       "inventory_id" int4 default nextval('inventory_id_seq') NOT NULL 
unique,
       "in_stock" int4 default 0,
       "name" character varying not null,
       "description" character varying not null,
       "cost_per_unit" money,
       "vendor_code" character varying,
       "vendor_name"  character varying,
       "vendor_address_id" int4   references address (address_id),
       primary key ("name")
);
grant update on inventory  to flip;
grant select on inventory  to flip;

create sequence "inventory_usage_id_seq" start 1 increment 1;
create table "inventory_usage" (
       "inventory_usage_id" int4 default nextval('inventory_usage_id_seq') 
NOT NULL unique,
       "inventory_id" int4   references inventory (inventory_id),
       "timestamp" timestamp default now(),
       "used" int4 not null,
       "printer" character varying,
       "real_lpi" real,
       "order_num" int4,
       primary key ("inventory_usage_id")
);
grant all on inventory_usage to flip;
grant all on inventory_usage_id_seq to flip;


create rule inventory_usage_insert_rule as
       on insert to inventory_usage
       do
	update inventory set inventory.in_stock=(select in_stock from inventory 
where 
inventory_usage.inventory_id=inventory.inventory_id)-inventory_usage.used
	       where inventory.inventory_id=inventory_usage.inventory_id;


I have also tried:

create rule inventory_usage_insert_rule as
       on insert to inventory_usage
       do
	update inventory set new.in_stock=old.in_stock-inventory_usage.used
	       where inventory.inventory_id=inventory_usage.inventory_id;

I think the second one would work if I put a in_stock column in the 
inventory_usage table. But this does not seam like a slick solution.


Thank you
Brad Paul

Responses

pgsql-novice by date

Next:From: kenDate: 2002-10-22 17:58:25
Subject: Re: Big Picture
Previous:From: Josh BerkusDate: 2002-10-22 17:03:26
Subject: Re: Big Picture

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group