1
|
/* To get the place trigger, replace taxonlabel with place and remove references
|
2
|
to matched_label_id. */
|
3
|
|
4
|
----- Functions
|
5
|
|
6
|
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)
|
7
|
RETURNS void AS
|
8
|
$BODY$
|
9
|
BEGIN
|
10
|
IF parent_id_ IS DISTINCT FROM old_parent_id THEN
|
11
|
DECLARE
|
12
|
-- These include the parent itself
|
13
|
old_ancestors integer[] := (
|
14
|
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
15
|
WHERE descendant_id = old_parent_id
|
16
|
);
|
17
|
new_ancestors integer[] := (
|
18
|
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
19
|
WHERE descendant_id = parent_id_
|
20
|
);
|
21
|
descendant_id_ taxonlabel_relationship.descendant_id%TYPE;
|
22
|
ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE;
|
23
|
BEGIN
|
24
|
FOR descendant_id_ IN -- also includes self
|
25
|
SELECT descendant_id
|
26
|
FROM taxonlabel_relationship
|
27
|
WHERE ancestor_id = new.taxonlabel_id
|
28
|
LOOP
|
29
|
-- Delete old parent's ancestors
|
30
|
DELETE FROM taxonlabel_relationship
|
31
|
WHERE descendant_id = descendant_id_
|
32
|
AND ancestor_id = ANY (old_ancestors)
|
33
|
;
|
34
|
|
35
|
-- Add new parent's ancestors
|
36
|
FOR ancestor_id_ IN
|
37
|
SELECT unnest(new_ancestors)
|
38
|
LOOP
|
39
|
BEGIN
|
40
|
INSERT INTO taxonlabel_relationship
|
41
|
(descendant_id, ancestor_id)
|
42
|
SELECT descendant_id_, ancestor_id_
|
43
|
;
|
44
|
EXCEPTION
|
45
|
WHEN unique_violation THEN
|
46
|
RAISE WARNING '%', SQLERRM;
|
47
|
END;
|
48
|
END LOOP;
|
49
|
END LOOP;
|
50
|
END;
|
51
|
END IF;
|
52
|
|
53
|
/* Note: We don't need an ON DELETE trigger to update the descendants'
|
54
|
ancestors when a node is deleted, because the
|
55
|
taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
|
56
|
END;
|
57
|
$BODY$
|
58
|
LANGUAGE plpgsql VOLATILE
|
59
|
COST 100;
|
60
|
|
61
|
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()
|
62
|
RETURNS trigger AS
|
63
|
$BODY$
|
64
|
BEGIN
|
65
|
/* Add "ancestor" for this node. This is useful for queries, because you
|
66
|
don't have to separately test if the leaf node is the one you're looking
|
67
|
for, in addition to that leaf node's ancestors. */
|
68
|
INSERT
|
69
|
INTO taxonlabel_relationship
|
70
|
(descendant_id, ancestor_id)
|
71
|
VALUES (new.taxonlabel_id, new.taxonlabel_id)
|
72
|
;
|
73
|
|
74
|
PERFORM taxonlabel_update_ancestors(new, new.parent_id);
|
75
|
PERFORM taxonlabel_update_ancestors(new,
|
76
|
NULLIF(new.matched_label_id, new.taxonlabel_id));
|
77
|
|
78
|
RETURN new;
|
79
|
END;
|
80
|
$BODY$
|
81
|
LANGUAGE plpgsql VOLATILE
|
82
|
COST 100;
|
83
|
|
84
|
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()
|
85
|
RETURNS trigger AS
|
86
|
$BODY$
|
87
|
BEGIN
|
88
|
PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id);
|
89
|
PERFORM taxonlabel_update_ancestors(new,
|
90
|
NULLIF(new.matched_label_id, new.taxonlabel_id),
|
91
|
NULLIF(old.matched_label_id, old.taxonlabel_id));
|
92
|
|
93
|
RETURN new;
|
94
|
END;
|
95
|
$BODY$
|
96
|
LANGUAGE plpgsql VOLATILE
|
97
|
COST 100;
|
98
|
|
99
|
----- Ancestors table
|
100
|
|
101
|
CREATE TABLE taxonlabel_relationship
|
102
|
(
|
103
|
descendant_id integer NOT NULL,
|
104
|
ancestor_id integer NOT NULL,
|
105
|
relationship relationship NOT NULL DEFAULT 'is included in'::relationship, -- The type of relationship.
|
106
|
CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id , ancestor_id ),
|
107
|
CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id)
|
108
|
REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
|
109
|
ON UPDATE CASCADE ON DELETE CASCADE,
|
110
|
CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)
|
111
|
REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
|
112
|
ON UPDATE CASCADE ON DELETE CASCADE
|
113
|
)
|
114
|
WITH (
|
115
|
OIDS=FALSE
|
116
|
);
|
117
|
ALTER TABLE taxonlabel_relationship
|
118
|
OWNER TO bien;
|
119
|
COMMENT ON TABLE taxonlabel_relationship
|
120
|
IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
|
121
|
COMMENT ON COLUMN taxonlabel_relationship.relationship IS 'The type of relationship.';
|
122
|
|
123
|
CREATE INDEX taxonlabel_relationship_descendants
|
124
|
ON taxonlabel_relationship
|
125
|
USING btree
|
126
|
(ancestor_id , descendant_id );
|
127
|
|
128
|
----- Triggers
|
129
|
|
130
|
CREATE TRIGGER taxonlabel_update_ancestors_on_insert
|
131
|
AFTER INSERT
|
132
|
ON taxonlabel
|
133
|
FOR EACH ROW
|
134
|
EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();
|
135
|
|
136
|
CREATE TRIGGER taxonlabel_update_ancestors_on_update
|
137
|
AFTER UPDATE
|
138
|
ON taxonlabel
|
139
|
FOR EACH ROW
|
140
|
EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update();
|