updateable view with join?

[Mike Harding <mvh@xxxxxxxxxxxxx>]

Suppose I have some tables and views like so for dog breeds and
breeders and their inventory where I also have a 'human' view of dog
inventory where the breed and breeder names are displayed instead of
the numerical IDs.  I've gotten inserts working but I'm stymied by
updates and deletes... any help?

Also, would inserting into this view be faster than the 3 SQL
statements I would otherwise need to look up the IDs and inserting
them and the count in the inv table?

This is a stripped down idealized example, so I apologize for any
syntax errors...

create table breeds (
       breed_id SERTIAL PRIMARY KEY,
       name TEXT NOT NULL UNIQUE
);

create table breeders (
       breeder_id SERIAL PRIMARY KEY,
       name TEXT NOT NULL UNIQUE
);

create table inv (
       breeder_id integer references breeder on update cascade,
       breed_id integer references breeds on update cascase,
       count integer NOT NULL,
       PRIMARY KEY(breeder_id,breed_id)
);

create view hinv
AS SELECT h.name AS breeder, d.name AS breed, count
FROM breeders h, breed d, inv
WHERE h.breeder_id = inv.breeder_id AND
d.breed_id = inv.breed_id;

CREATE hinv_ins AS ON INSERT TO hinv
       DO INSTEAD
       INSERT INTO inv(breeder_id,breed_id,count)
       SELECT h.breeder_id, d.breed_id, NEW.count
       WHERE h.name = NEW.breeder
       AND d.name = NEW.breed;

update/delete???

Thanks,

Mike H.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend