Revision 5828
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 693 | 693 |
|
| 694 | 694 |
|
| 695 | 695 |
-- |
| 696 |
-- Name: taxonlabel_update_ancestors(taxonlabel, integer); Type: FUNCTION; Schema: public; Owner: - |
|
| 696 |
-- Name: taxonlabel_update_ancestors(taxonlabel, integer, integer); Type: FUNCTION; Schema: public; Owner: -
|
|
| 697 | 697 |
-- |
| 698 | 698 |
|
| 699 |
CREATE FUNCTION taxonlabel_update_ancestors(new taxonlabel, old_parent_id integer DEFAULT NULL::integer) RETURNS void |
|
| 699 |
CREATE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer) RETURNS void
|
|
| 700 | 700 |
LANGUAGE plpgsql |
| 701 | 701 |
AS $$ |
| 702 |
DECLARE |
|
| 703 |
-- Use matched_label_id's ancestors instead if available |
|
| 704 |
parent_id_ taxonlabel.taxonlabel_id%TYPE := COALESCE( |
|
| 705 |
NULLIF(new.matched_label_id, new.taxonlabel_id), new.parent_id); |
|
| 706 | 702 |
BEGIN |
| 707 | 703 |
IF parent_id_ IS DISTINCT FROM old_parent_id THEN |
| 708 | 704 |
DECLARE |
| ... | ... | |
| 771 | 767 |
VALUES (new.taxonlabel_id, new.taxonlabel_id) |
| 772 | 768 |
; |
| 773 | 769 |
|
| 774 |
PERFORM taxonlabel_update_ancestors(new); |
|
| 770 |
PERFORM taxonlabel_update_ancestors(new, new.parent_id); |
|
| 771 |
PERFORM taxonlabel_update_ancestors(new, |
|
| 772 |
NULLIF(new.matched_label_id, new.taxonlabel_id)); |
|
| 775 | 773 |
|
| 776 | 774 |
RETURN new; |
| 777 | 775 |
END; |
| ... | ... | |
| 786 | 784 |
LANGUAGE plpgsql |
| 787 | 785 |
AS $$ |
| 788 | 786 |
BEGIN |
| 789 |
PERFORM taxonlabel_update_ancestors(new, COALESCE( |
|
| 790 |
NULLIF(old.matched_label_id, old.taxonlabel_id), old.parent_id)); |
|
| 787 |
PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id); |
|
| 788 |
PERFORM taxonlabel_update_ancestors(new, |
|
| 789 |
NULLIF(new.matched_label_id, new.taxonlabel_id), |
|
| 790 |
NULLIF(old.matched_label_id, old.taxonlabel_id)); |
|
| 791 | 791 |
|
| 792 | 792 |
RETURN new; |
| 793 | 793 |
END; |
Also available in: Unified diff
schemas/vegbien.sql: taxonlabel_update_ancestors(): Include ancestors for both parent_id and matched_label_id rather than just one or the other. This avoids needing to delete existing ancestors for the parent_id when a matched_label_id is added and overrides it. This should reduce the TNRS import time if the slowdown was due to the need to delete parent_id ancestors when later adding a matched_label_id (which only occurs in a separate step in the TNRS datasource).