Skip site navigation (1) Skip section navigation (2)

RI / foreign key on VIEW

From: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: RI / foreign key on VIEW
Date: 2004-02-07 12:39:23
Message-ID: 4024DC7B.8000704@e-trolley.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Reshat SabiqDate: 2004-02-07 20:01:42
Subject: Re: RI / foreign key on VIEW
Previous:From: Josh BerkusDate: 2004-02-06 18:13:20
Subject: Re: dat file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group