pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger to maintain relationships
3+ messages / 2 participants
[nested] [flat]
* trigger to maintain relationships
@ 2002-12-11 17:07 David M <davidgm0@ucia.gov>
0 siblings, 1 reply; 3+ messages in thread
From: David M @ 2002-12-11 17:07 UTC (permalink / raw)
To: pgsql-sql
I am maintaining a set of hierarchical data that looks a lot like a
tree. (And my SQL is very rusty. And I'm new to postgres.)
Questions:
-------------
1.) Is the following a reasonable solution? Is there a
postgres-specific way to handle this better? Is there a good generic
SQL way to handle this?
2.) Can I write pure "SQL" triggers to handle this? Am I getting close
in my first cut (below)?
3.) Any other ideas/suggestions?
I have one table with essentially the nodes of a tree:
nodes
------
node_id integer
parent_id integer references nodes(node_id)
...and other descriptive columns...
I want an easy way to find all the elements of a subtree. Not being
able to think of a good declarative solution, I was thinking about
cheating and maintaining an ancestors table:
ancestors
-----------
node_id integer
ancestor_id integer references nodes(node_id)
I figured I could populate the ancestors table via trigger(s) on the
nodes table. Then I should be able to find a whole subtree of node X
with something like:
select *
from nodes
where node_id in (
select node_id
from ancestors
where ancestor_id = X)
Here's my best guess so far at the triggers (but, obviously, no luck so
far):
--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();
--delete trigger
create function pr_tr_d_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;'
language sql;
create trigger tr_d_nodes after insert
on nodes for each row
execute procedure pr_tr_d_nodes();
--update trigger
create function pr_tr_u_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_u_nodes after insert
on nodes for each row
execute procedure pr_tr_u_nodes();
I realize the update trigger could be handled a multitude of ways and
that my first guess may be pretty lousy. But I figured the
insert/update triggers would be pretty straightforward. Am I missing
something basic? I also tried things like (following the one example in
the reference manual):
--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;
return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: trigger to maintain relationships
@ 2002-12-11 17:35 David M <davidgm0@ucia.gov>
parent: David M <davidgm0@ucia.gov>
0 siblings, 1 reply; 3+ messages in thread
From: David M @ 2002-12-11 17:35 UTC (permalink / raw)
To: pgsql-sql
I think I figured out my join syntax error (sorry for confusing the issue
with noise like that). I'd still be interested in general comments on
design.
FYI, join should've looked like:
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from NEW left outer join ancestors on (NEW.parent_id =
ancestors.node_id);
return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();
David M wrote:
> I am maintaining a set of hierarchical data that looks a lot like a
> tree. (And my SQL is very rusty. And I'm new to postgres.)
>
> Questions:
> -------------
> 1.) Is the following a reasonable solution? Is there a
> postgres-specific way to handle this better? Is there a good generic
> SQL way to handle this?
> 2.) Can I write pure "SQL" triggers to handle this? Am I getting close
> in my first cut (below)?
> 3.) Any other ideas/suggestions?
>
> I have one table with essentially the nodes of a tree:
>
> nodes
> ------
> node_id integer
> parent_id integer references nodes(node_id)
> ...and other descriptive columns...
>
> I want an easy way to find all the elements of a subtree. Not being
> able to think of a good declarative solution, I was thinking about
> cheating and maintaining an ancestors table:
>
> ancestors
> -----------
> node_id integer
> ancestor_id integer references nodes(node_id)
>
> I figured I could populate the ancestors table via trigger(s) on the
> nodes table. Then I should be able to find a whole subtree of node X
> with something like:
>
> select *
> from nodes
> where node_id in (
> select node_id
> from ancestors
> where ancestor_id = X)
>
> Here's my best guess so far at the triggers (but, obviously, no luck so
> far):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> --delete trigger
> create function pr_tr_d_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;'
> language sql;
> create trigger tr_d_nodes after insert
> on nodes for each row
> execute procedure pr_tr_d_nodes();
>
> --update trigger
> create function pr_tr_u_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;
>
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_u_nodes after insert
> on nodes for each row
> execute procedure pr_tr_u_nodes();
>
> I realize the update trigger could be handled a multitude of ways and
> that my first guess may be pretty lousy. But I figured the
> insert/update triggers would be pretty straightforward. Am I missing
> something basic? I also tried things like (following the one example in
> the reference manual):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;
>
> return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: trigger to maintain relationships
@ 2002-12-12 01:02 Josh Berkus <josh@agliodbs.com>
parent: David M <davidgm0@ucia.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-12-12 01:02 UTC (permalink / raw)
To: David M <davidgm0@ucia.gov>; pgsql-sql
David,
> FYI, join should've looked like:
>
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from NEW left outer join ancestors on (NEW.parent_id =
> ancestors.node_id);
>
> return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
Ummm ... no.
Within the trigger produre, NEW is a record variable, and its fields
are values. You cannot SELECT from NEW. You're also missing the parts
of a PLPGSQL procedure. What you want is:
create function pr_tr_i_nodes() returns opaque
> as '
DECLARE v_ancestor INT;
BEGIN
SELECT ancestor_id INTO v_ancestor
FROM ancestors WHERE ancestors.node_id = NEW.parent_id;
INSERT INTO ancestors
VALUES ( NEW.node_id, v_ancestor );
> return NEW;
END;'
> language 'plpgsql';
-Josh Berkus
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-12-12 01:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-11 17:07 trigger to maintain relationships David M <davidgm0@ucia.gov>
2002-12-11 17:35 ` David M <davidgm0@ucia.gov>
2002-12-12 01:02 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox