RI / foreign key on VIEW

Поиск
Список
Период
Сортировка
Искать
От
Nabil Sayegh
Тема
RI / foreign key on VIEW
Дата
Msg-id
4024DC7B.8000704@e-trolley.de
Список
Дерево обсуждения
RI / foreign key on VIEW Nabil Sayegh <postgresql@e-trolley.de>
Re: RI / foreign key on VIEW Reshat Sabiq <sabiq@purdue.edu>
Re: RI / foreign key on VIEW Bruno Wolff III <bruno@wolff.to>
Re: RI / foreign key on VIEW Nabil Sayegh <postgresql@e-trolley.de>
Hi all,

I have a db structure with a VIEW that I need to reference (ON DELETE CASCADE).
I know that it is not possible to have references on a VIEW, but maybe someone has some TRIGGERs at 
hand that do this job.

Here's an example to play with:

------------------------------------------------------------------------------------------------
--
-- This table holds objects (many different sorts)
--
CREATE TABLE objekt (
  id_objekt SERIAL PRIMARY KEY,
  handle text
);

--
-- This table makes (some) objects buyable (products)
--
CREATE TABLE price (
  id_price SERIAL PRIMARY KEY,
  id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL,
  price float
);

--
-- This table expires some products
--
CREATE TABLE expire (
  id_expire SERIAL PRIMARY KEY,
  id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL,
  expire date NOT NULL
);

--
-- This is the virtual product table
--
CREATE VIEW product AS SELECT * FROM objekt JOIN price USING (id_objekt) LEFT OUTER JOIN expire 
USING (id_objekt) WHERE expire IS NULL OR expire > now();

INSERT INTO objekt (handle) values ('product 1');
INSERT INTO objekt (handle) values ('product 2');
INSERT INTO objekt (handle) values ('product 3');

INSERT INTO price (id_objekt, price) values (1, 1.99);
INSERT INTO price (id_objekt, price) values (2, 2.99);
INSERT INTO price (id_objekt, price) values (3, 3.99);

INSERT INTO expire (id_objekt, expire) values (2, '01.01.1980');

--
-- This is what I need to be done with TRIGGERs
--
CREATE TABLE basket (
  id_basket SERIAL PRIMARY KEY,
  id_user int,
  id_objekt int REFERENCES product ON DELETE CASCADE NOT NULL
);

-------------------------------------------------------------------------------------------------
ERROR:  referenced relation "product" is not a table

TIA
-- 
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de
-- 
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de
В списке pgsql-novice по дате отправления
От: Josh Berkus
Дата:
Сообщение: Re: dat file
От: Rob Mosher
Дата:
Сообщение: Re: Inserting a Null date.
FAQ