Re: Rules - Mailing list pgsql-general

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