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

Re: RI / foreign key on VIEW

From: Reshat Sabiq <sabiq(at)purdue(dot)edu>
To: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: RI / foreign key on VIEW
Date: 2004-02-07 20:01:42
Message-ID: 40254426.3050404@purdue.edu (view raw or flat)
Thread:
Lists: pgsql-novice

Nabil Sayegh wrote:

> 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

Last i heard VIEWs aren't yet updateable, but there are plans to make 
them so sometimes in the future?

-- 
Sincerely,
Reshat.

-------------------------------------------------------------------------------------------
If you see my certificate with this message, you should be able to send me encrypted e-mail. 
Please consult your e-mail client for details if you would like to do that.

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-02-07 20:51:56
Subject: Re: pgplpsql and the command line
Previous:From: Nabil SayeghDate: 2004-02-07 12:39:23
Subject: RI / foreign key on VIEW

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