>> Planning on witting a rule for a view, and i was wondering if anyone
>> could suggest a good Internet resource?
>
> http://www.postgresql.org
>
> (Sorry, couldn't resist.)
>
But here is a simple working example of making a view updatable:
CREATE TABLE consumable (
consumable_pk serial NOT NULL,
consumable_type_pk integer NOT NULL,
manufacturer_pk integer NOT NULL,
part_number character varying(18) NOT NULL,
quantity_on_hand integer,
reorder_quantity integer
) WITHOUT OIDS;
CREATE VIEW consumables AS
SELECT
manufacturer.manufacturer_pk,
consumable.consumable_pk,
manufacturer.manufacturer,
consumable_type.consumable_type,
consumable.part_number,
consumable.quantity_on_hand,
reorder_quantity,
CASE
WHEN (reorder_quantity > consumable.quantity_on_hand)
THEN (reorder_quantity - consumable.quantity_on_hand)
ELSE 0 END AS requisition_quantity
FROM ((consumable
LEFT JOIN consumable_type USING (consumable_type_pk))
LEFT JOIN manufacturer USING (manufacturer_pk))
ORDER BY
manufacturer.manufacturer,
consumable_type.consumable_type,
consumable.part_number;
CREATE RULE consumables_rd AS ON DELETE TO consumables DO INSTEAD NOTHING;
CREATE RULE consumables_ri AS ON INSERT TO consumables DO INSTEAD NOTHING;
CREATE RULE consumables_ru AS ON UPDATE TO consumables DO INSTEAD
UPDATE consumable
SET
part_number = new.part_number,
quantity_on_hand = new.quantity_on_hand,
reorder_quantity = new.reorder_quantity
WHERE ((consumable.consumable_pk = old.consumable_pk)
AND (consumable.manufacturer_pk = old.manufacturer_pk));
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
