Revision 5831
Added by Aaron Marcuse-Kubitza over 12 years ago
| vegbien.sql | ||
|---|---|---|
| 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 |
|
Also available in: Unified diff
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