Rules

Have you looked at the html docs? It looks like "34.2. Views and the Rule
System" might be what you are looking for, but I didn't check it closely.

On Thursday 02 December 2004 05:34 pm, Jamie Deppeler saith:

HI,

Planning on witting a rule for a view, and i was wondering if anyone
could suggest a good Internet resource?

thx

--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com

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

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