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

Search Discussions

  • Reshat Sabiq at Feb 7, 2004 at 8:01 pm

    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.
  • Nabil Sayegh at Feb 8, 2004 at 1:05 am

    Reshat Sabiq wrote:


    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.
    [...]
    Last i heard VIEWs aren't yet updateable, but there are plans to make
    them so sometimes in the future?
    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
  • Bruno Wolff III at May 9, 2004 at 3:14 pm

    On Sat, Feb 07, 2004 at 14:01:42 -0600, Reshat Sabiq wrote:

    Last i heard VIEWs aren't yet updateable, but there are plans to make
    them so sometimes in the future?
    You can make updateable views now using rules. There has been some talk
    of handling simple cases automatically.
  • Nabil Sayegh at Feb 8, 2004 at 2:02 pm

    Idefix wrote:
    Well, you don't need to reference the VIEW (doesn't make sense anyway)
    but your OBJEKT table.
    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.
    Further the Referencing should start from your OBJEKT table, so you
    could cascade through all tables without any problem.
    --
    -- 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();
    TFYH
    --
    e-Trolley Sayegh & John, Nabil Sayegh
    Tel.: 0700 etrolley /// 0700 38765539
    Fax.: +49 69 8299381-8
    PGP : http://www.e-trolley.de

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 7, '04 at 12:39p
activeMay 9, '04 at 3:14p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase