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

Re: How can I do this?

From: "Alan Young" <alany(at)qwest(dot)net>
To: "Joel Burton" <jburton(at)scw(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How can I do this?
Date: 2000-12-21 22:24:51
Message-ID: 014001c06b9c$d73ad080$d44f58cf@idiglobal.com (view raw or flat)
Thread:
Lists: pgsql-novice
> 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



In response to

pgsql-novice by date

Next:From: David MerrillDate: 2000-12-21 22:30:16
Subject: error "11"
Previous:From: Joel BurtonDate: 2000-12-21 20:17:02
Subject: Re: How can I do this?

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