Re: RI / foreign key on VIEW

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.