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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Merrill | 2000-12-21 22:30:16 | error "11" |
Previous Message | Joel Burton | 2000-12-21 20:17:02 | Re: How can I do this? |