updateable view with join?

Поиск
Список
Период
Сортировка
От Mike Harding
Тема updateable view with join?
Дата
Msg-id 20050211182032.67E161745D@bsd.mvh
обсуждение исходный текст
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: fiona
Дата:
Сообщение: regular expression
Следующее
От: Shridhar Daithankar
Дата:
Сообщение: Re: PostgreSQL vs. MySQL vs. Oracle, 2005 report card