On Thu, 21 Dec 2000, Alan Young wrote:

reference a field within the same table (you'll see why later)
have a field automatically changed to another row's field value upon
deletion of the referenced field

create table category (
catid serial primary key,
parentid int references category ( catid ) on delete <?trigger here?>,
catname text unique,
catdesc text
);

I know I need to create a trigger but I'm not sure how to do that. I've
read the docs, but an example would be great for this particular need.
You can use a trigger, but not w/syntax like this. REFERENCES... ON
DELETE... only allows you to to do regular referential integrity stuff
(set the field to null, block deletions, etc.), not look up a better value
and put it there.

You want to create a trigger with something like:

CREATE TRIGGER foo BEFORE DELETE ON category FOR EACH ROW EXECUTE
PROCEDURE foo();

Your function foo() can dig the new value you want out and update this for
all the related records.

Can you explain what it is that you want to do? It sounds like you're
building a tree here, and there might be a better strategy overall than
the one you're starting with.

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Search Discussions

  • Alan Young at Dec 21, 2000 at 10:24 pm

    You can use a trigger, but not w/syntax like this. REFERENCES... ON
    DELETE... only allows you to to do regular referential integrity stuff
    (set the field to null, block deletions, etc.), not look up a better value
    and put it there.

    You want to create a trigger with something like:

    CREATE TRIGGER foo BEFORE DELETE ON category FOR EACH ROW EXECUTE
    PROCEDURE foo();
    So the parentid would then just be

    parentid int references category ( catid ),

    ?
    Your function foo() can dig the new value you want out and update this for
    all the related records.
    I'm still not clear on how I would get the appropriate data into the
    function.

    CREATE FUNCTION updatecat ( ??? )
    RETURNS int
    AS 'A = select parentid from category where catid=<catid to be
    deleted--where does this come from?>;
    update category set parentid=A where parentid=<catid to be deleted>;'
    LANGUAGE 'sql';

    How do I get the appropriate info into the query? Also, I'm fairly certain
    I can do that select/update as a subselect but I'm not sure how to go about
    doing that. I'm new to subqueries as well.
    Can you explain what it is that you want to do? It sounds like you're
    building a tree here, and there might be a better strategy overall than
    the one you're starting with.
    I want to have a category table that supports sub categories. Easy enough,
    the table I defined in my original post works just fine for that. But what
    I want to happen is that a subcategory will be reassigned automagically to
    it's parent category's parent category upon deletion of the parent category.
    Ummm ... I'm not sure how to say that any better.

    For example, I have the following categorys

    catid | parentid | catname | catdesc
    ========================
    0 | | TOP | Top level category
    1 | 0 | cat1 | cat one
    2 | 1 | cat1.1 | cat one:one
    3 | 2 | cat1.1.1 | cat one:two:three

    delete from category where catid=2;

    I want the following to happen automagically.

    catid | parentid | catname | catdesc
    ========================
    0 | | TOP | Top level category
    1 | 0 | cat1 | cat one
    3 | 1 | cat1.1.1 | cat one:two

    Does that make more sense?

    Alan Young
    Programmer/Analyst
    IDIGlobal.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 21, '00 at 8:17p
activeDec 21, '00 at 10:24p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Joel Burton: 1 post Alan Young: 1 post

People

Translate

site design / logo © 2022 Grokbase