Revision 5518
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 478 | 478 |
CREATE FUNCTION taxonconcept_update_ancestors() RETURNS trigger |
| 479 | 479 |
LANGUAGE plpgsql |
| 480 | 480 |
AS $$ |
| 481 |
DECLARE |
|
| 482 |
-- Use matched_concept_id's ancestors instead if available |
|
| 483 |
parent_id_ taxonconcept.taxonconcept_id%TYPE := |
|
| 484 |
COALESCE(new.matched_concept_id, new.parent_id); |
|
| 481 | 485 |
BEGIN |
| 482 | 486 |
-- Delete existing ancestors |
| 483 | 487 |
DELETE FROM taxonconcept_ancestor |
| 484 | 488 |
WHERE taxonconcept_id = new.taxonconcept_id; |
| 485 | 489 |
|
| 486 |
IF new.parent_id IS NOT NULL THEN
|
|
| 490 |
IF parent_id_ IS NOT NULL THEN
|
|
| 487 | 491 |
-- Copy parent's ancestors to this node's ancestors |
| 488 | 492 |
INSERT |
| 489 | 493 |
INTO taxonconcept_ancestor |
| ... | ... | |
| 491 | 495 |
SELECT |
| 492 | 496 |
new.taxonconcept_id, ancestor_id |
| 493 | 497 |
FROM taxonconcept_ancestor |
| 494 |
WHERE taxonconcept_id = new.parent_id
|
|
| 498 |
WHERE taxonconcept_id = parent_id_
|
|
| 495 | 499 |
; |
| 496 | 500 |
END IF; |
| 497 | 501 |
|
| ... | ... | |
| 3009 | 3013 |
-- Name: TABLE taxonconcept_ancestor; Type: COMMENT; Schema: public; Owner: - |
| 3010 | 3014 |
-- |
| 3011 | 3015 |
|
| 3012 |
COMMENT ON TABLE taxonconcept_ancestor IS 'taxonconcept''s ancestor cross link table.';
|
|
| 3016 |
COMMENT ON TABLE taxonconcept_ancestor IS 'Stores the accepted ancestors of a taxonconcept. Auto-populated, so should not be manually modified.';
|
|
| 3013 | 3017 |
|
| 3014 | 3018 |
|
| 3015 | 3019 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxonconcept: taxonconcept_update_ancestors(): Use matched_concept_id's ancestors instead if available. (Recursively applied, this will use the ancestors of the accepted concept.) This facilitates finding all children of and matches to an accepted concept, which will all have an entry for that concept in taxonconcept_ancestor. Note that the concept's own parents will not be indexed in taxonconcept_ancestor, because only accepted ancestors are now stored in taxonconcept_ancestor. Documented that taxonconcept_ancestor now stores the accepted ancestors of a taxonconcept.