updateable view with join?

From: Mike Harding <mvh(at)ix(dot)netcom(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: updateable view with join?
Date: 2005-02-11 18:20:32
Message-ID: 20050211182032.67E161745D@bsd.mvh
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Mark Harrison 2005-02-11 18:20:47 size in bytes of a table?
Previous Message Bruce Momjian 2005-02-11 18:15:57 Re: PostgreSQL Features