| 1 |
5283
|
aaronmk
|
/* To get the place trigger, just search and replace taxonconcept with place. */
|
| 2 |
1561
|
aaronmk
|
|
| 3 |
1558
|
aaronmk
|
----- Trigger function
|
| 4 |
|
|
|
| 5 |
5283
|
aaronmk
|
-- Function: taxonconcept_update_ancestors()
|
| 6 |
1558
|
aaronmk
|
|
| 7 |
5283
|
aaronmk
|
-- DROP FUNCTION taxonconcept_update_ancestors();
|
| 8 |
1558
|
aaronmk
|
|
| 9 |
5283
|
aaronmk
|
CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors()
|
| 10 |
1558
|
aaronmk
|
RETURNS trigger AS
|
| 11 |
|
|
$BODY$
|
| 12 |
|
|
BEGIN
|
| 13 |
1559
|
aaronmk
|
-- Delete existing ancestors
|
| 14 |
5283
|
aaronmk
|
DELETE FROM taxonconcept_ancestor
|
| 15 |
|
|
WHERE taxonconcept_id = new.taxonconcept_id;
|
| 16 |
1559
|
aaronmk
|
|
| 17 |
1558
|
aaronmk
|
IF new.parent_id IS NOT NULL THEN
|
| 18 |
|
|
-- Copy parent's ancestors to this node's ancestors
|
| 19 |
|
|
INSERT
|
| 20 |
5283
|
aaronmk
|
INTO taxonconcept_ancestor
|
| 21 |
|
|
(taxonconcept_id, ancestor_id)
|
| 22 |
1558
|
aaronmk
|
SELECT
|
| 23 |
5283
|
aaronmk
|
new.taxonconcept_id, ancestor_id
|
| 24 |
|
|
FROM taxonconcept_ancestor
|
| 25 |
|
|
WHERE taxonconcept_id = new.parent_id
|
| 26 |
1558
|
aaronmk
|
;
|
| 27 |
|
|
END IF;
|
| 28 |
1559
|
aaronmk
|
|
| 29 |
|
|
-- Add "ancestor" for this node
|
| 30 |
|
|
/* This is useful for queries, because you don't have to separately test if
|
| 31 |
|
|
the leaf node is the one you're looking for, in addition to that leaf node's
|
| 32 |
|
|
ancestors. */
|
| 33 |
|
|
INSERT
|
| 34 |
5283
|
aaronmk
|
INTO taxonconcept_ancestor
|
| 35 |
|
|
(taxonconcept_id, ancestor_id)
|
| 36 |
|
|
VALUES (new.taxonconcept_id, new.taxonconcept_id)
|
| 37 |
1559
|
aaronmk
|
;
|
| 38 |
|
|
|
| 39 |
|
|
-- Tell immediate children to update their ancestors lists, which will
|
| 40 |
|
|
-- recursively tell all descendants
|
| 41 |
5283
|
aaronmk
|
UPDATE taxonconcept
|
| 42 |
|
|
SET taxonconcept_id = taxonconcept_id -- need at least one SET statement
|
| 43 |
|
|
-- Add COALESCE() to enable using taxonconcept_unique index for lookup
|
| 44 |
|
|
WHERE COALESCE(parent_id, 2147483647) = new.taxonconcept_id
|
| 45 |
5512
|
aaronmk
|
AND taxonconcept_id != new.taxonconcept_id -- avoid infinite recursion
|
| 46 |
1559
|
aaronmk
|
;
|
| 47 |
|
|
|
| 48 |
|
|
/* Note: We don't need an ON DELETE trigger to update the descendants'
|
| 49 |
5283
|
aaronmk
|
ancestors when a node is deleted, because the taxonconcept.parent_id foreign
|
| 50 |
|
|
key is ON DELETE CASCADE, which just removes all the descendants anyway. */
|
| 51 |
1559
|
aaronmk
|
|
| 52 |
1558
|
aaronmk
|
RETURN new;
|
| 53 |
|
|
END;
|
| 54 |
|
|
$BODY$
|
| 55 |
|
|
LANGUAGE plpgsql VOLATILE
|
| 56 |
|
|
COST 100;
|
| 57 |
5283
|
aaronmk
|
ALTER FUNCTION taxonconcept_update_ancestors()
|
| 58 |
1558
|
aaronmk
|
OWNER TO bien;
|
| 59 |
|
|
|
| 60 |
|
|
----- Ancestors table
|
| 61 |
|
|
|
| 62 |
5283
|
aaronmk
|
-- Table: taxonconcept_ancestor
|
| 63 |
1558
|
aaronmk
|
|
| 64 |
5283
|
aaronmk
|
-- DROP TABLE taxonconcept_ancestor;
|
| 65 |
1558
|
aaronmk
|
|
| 66 |
5283
|
aaronmk
|
CREATE TABLE taxonconcept_ancestor
|
| 67 |
1558
|
aaronmk
|
(
|
| 68 |
5283
|
aaronmk
|
taxonconcept_id integer NOT NULL,
|
| 69 |
1558
|
aaronmk
|
ancestor_id integer NOT NULL,
|
| 70 |
5283
|
aaronmk
|
CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ),
|
| 71 |
|
|
CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)
|
| 72 |
|
|
REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
|
| 73 |
1558
|
aaronmk
|
ON UPDATE CASCADE ON DELETE CASCADE,
|
| 74 |
5283
|
aaronmk
|
CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)
|
| 75 |
|
|
REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
|
| 76 |
1558
|
aaronmk
|
ON UPDATE CASCADE ON DELETE CASCADE
|
| 77 |
|
|
)
|
| 78 |
|
|
WITH (
|
| 79 |
|
|
OIDS=FALSE
|
| 80 |
|
|
);
|
| 81 |
5283
|
aaronmk
|
ALTER TABLE taxonconcept_ancestor
|
| 82 |
1558
|
aaronmk
|
OWNER TO bien;
|
| 83 |
|
|
|
| 84 |
|
|
----- Trigger
|
| 85 |
|
|
|
| 86 |
5283
|
aaronmk
|
-- Trigger: taxonconcept_update_ancestors on taxonconcept
|
| 87 |
1558
|
aaronmk
|
|
| 88 |
5283
|
aaronmk
|
-- DROP TRIGGER taxonconcept_update_ancestors ON taxonconcept;
|
| 89 |
1558
|
aaronmk
|
|
| 90 |
5283
|
aaronmk
|
CREATE TRIGGER taxonconcept_update_ancestors
|
| 91 |
1558
|
aaronmk
|
AFTER INSERT OR UPDATE
|
| 92 |
5283
|
aaronmk
|
ON taxonconcept
|
| 93 |
1558
|
aaronmk
|
FOR EACH ROW
|
| 94 |
5283
|
aaronmk
|
EXECUTE PROCEDURE taxonconcept_update_ancestors();
|
