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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Reshat Sabiq wrote:
Thanks for your answer, but I don't need updateable VIEWs.
I need referenceable VIEWs.

TFYH
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Idefix wrote:
The idea was that
- only special objects (i.e. products) can be in the baskets
- only products that aren't yet expired can be in the baskets.

I agree, that basket should be an objekt, but due to compatibility reasons I'd
rather leave it as a special table.

TFYH
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Sat, Feb 07, 2004 at 14:01:42 -0600,
Reshat Sabiq <sabiq (AT) purdue (DOT) edu> wrote:
You can make updateable views now using rules. There has been some talk
of handling simple cases automatically.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly