| 521 |
521 |
LANGUAGE plpgsql
|
| 522 |
522 |
AS $$
|
| 523 |
523 |
BEGIN
|
| 524 |
|
new.canon_label_id := new.taxonlabel_id; -- make self-reference
|
|
524 |
new := taxonlabel_set_canon_label_id(new);
|
|
525 |
|
| 525 |
526 |
RETURN new;
|
| 526 |
527 |
END;
|
| 527 |
528 |
$$;
|
| ... | ... | |
| 535 |
536 |
LANGUAGE plpgsql
|
| 536 |
537 |
AS $$
|
| 537 |
538 |
BEGIN
|
| 538 |
|
IF new.matched_label_id IS DISTINCT FROM old.matched_label_id THEN
|
| 539 |
|
IF new.matched_label_id IS NOT NULL THEN
|
| 540 |
|
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference
|
| 541 |
|
new.canon_label_id := new.taxonlabel_id; -- make self-reference
|
| 542 |
|
ELSE -- propagate from matched label
|
| 543 |
|
new.canon_label_id := (
|
| 544 |
|
SELECT canon_label_id
|
| 545 |
|
FROM taxonlabel
|
| 546 |
|
WHERE taxonlabel_id = new.matched_label_id
|
| 547 |
|
);
|
| 548 |
|
END IF;
|
| 549 |
|
|
| 550 |
|
-- Update canon_label_id on labels that resolve to this label
|
| 551 |
|
UPDATE taxonlabel
|
| 552 |
|
SET canon_label_id = new.canon_label_id
|
| 553 |
|
WHERE matched_label_id = new.taxonlabel_id
|
| 554 |
|
AND taxonlabel_id != new.taxonlabel_id -- avoid infinite recursion
|
| 555 |
|
;
|
| 556 |
|
ELSE -- no matched taxonlabel
|
| 557 |
|
new.canon_label_id := new.taxonlabel_id; -- make self-reference
|
| 558 |
|
END IF;
|
| 559 |
|
END IF;
|
|
539 |
new := taxonlabel_set_canon_label_id(new, old.matched_label_id, true);
|
|
540 |
|
| 560 |
541 |
RETURN new;
|
| 561 |
542 |
END;
|
| 562 |
543 |
$$;
|
| ... | ... | |
| 693 |
674 |
|
| 694 |
675 |
|
| 695 |
676 |
--
|
|
677 |
-- Name: taxonlabel_set_canon_label_id(taxonlabel, integer, boolean); Type: FUNCTION; Schema: public; Owner: -
|
|
678 |
--
|
|
679 |
|
|
680 |
CREATE FUNCTION taxonlabel_set_canon_label_id(new taxonlabel, old_matched_label_id integer DEFAULT NULL::integer, is_update boolean DEFAULT false) RETURNS taxonlabel
|
|
681 |
LANGUAGE plpgsql
|
|
682 |
AS $$
|
|
683 |
BEGIN
|
|
684 |
IF new.matched_label_id IS DISTINCT FROM old_matched_label_id THEN
|
|
685 |
IF new.matched_label_id IS NOT NULL THEN
|
|
686 |
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference
|
|
687 |
new.canon_label_id := new.taxonlabel_id; -- make self-reference
|
|
688 |
ELSE -- propagate from matched label
|
|
689 |
new.canon_label_id := (
|
|
690 |
SELECT canon_label_id
|
|
691 |
FROM taxonlabel
|
|
692 |
WHERE taxonlabel_id = new.matched_label_id
|
|
693 |
);
|
|
694 |
END IF;
|
|
695 |
|
|
696 |
IF is_update THEN
|
|
697 |
-- Update canon_label_id on labels that resolve to this label
|
|
698 |
UPDATE taxonlabel
|
|
699 |
SET canon_label_id = new.canon_label_id
|
|
700 |
WHERE matched_label_id = new.taxonlabel_id
|
|
701 |
AND taxonlabel_id != new.taxonlabel_id -- no infinite recursion
|
|
702 |
;
|
|
703 |
END IF;
|
|
704 |
ELSE -- no matched taxonlabel
|
|
705 |
new.canon_label_id := new.taxonlabel_id; -- make self-reference
|
|
706 |
END IF;
|
|
707 |
END IF;
|
|
708 |
|
|
709 |
RETURN new;
|
|
710 |
END;
|
|
711 |
$$;
|
|
712 |
|
|
713 |
|
|
714 |
--
|
| 696 |
715 |
-- Name: taxonlabel_update_ancestors(taxonlabel, integer, integer); Type: FUNCTION; Schema: public; Owner: -
|
| 697 |
716 |
--
|
| 698 |
717 |
|
schemas/vegbien.sql: taxonlabel_2_set_canon_label_id_on_insert(): Fixed bug where also need to set canon_label_id based on matched_label_id here, not just in taxonlabel_2_set_canon_label_id_on_update(), because the matched_label_id could be specified when the taxonlabel is first created