| 492 |
492 |
|
| 493 |
493 |
|
| 494 |
494 |
--
|
| 495 |
|
-- Name: taxonlabel_2_set_canon_label_id(); Type: FUNCTION; Schema: public; Owner: -
|
|
495 |
-- Name: taxonlabel_2_set_canon_label_id_on_insert(); Type: FUNCTION; Schema: public; Owner: -
|
| 496 |
496 |
--
|
| 497 |
497 |
|
| 498 |
|
CREATE FUNCTION taxonlabel_2_set_canon_label_id() RETURNS trigger
|
|
498 |
CREATE FUNCTION taxonlabel_2_set_canon_label_id_on_insert() RETURNS trigger
|
| 499 |
499 |
LANGUAGE plpgsql
|
| 500 |
500 |
AS $$
|
| 501 |
501 |
BEGIN
|
| 502 |
|
IF new.matched_label_id IS NOT NULL THEN
|
| 503 |
|
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference
|
|
502 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference
|
|
503 |
RETURN new;
|
|
504 |
END;
|
|
505 |
$$;
|
|
506 |
|
|
507 |
|
|
508 |
--
|
|
509 |
-- Name: taxonlabel_2_set_canon_label_id_on_update(); Type: FUNCTION; Schema: public; Owner: -
|
|
510 |
--
|
|
511 |
|
|
512 |
CREATE FUNCTION taxonlabel_2_set_canon_label_id_on_update() RETURNS trigger
|
|
513 |
LANGUAGE plpgsql
|
|
514 |
AS $$
|
|
515 |
BEGIN
|
|
516 |
IF new.matched_label_id IS DISTINCT FROM old.matched_label_id THEN
|
|
517 |
IF new.matched_label_id IS NOT NULL THEN
|
|
518 |
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference
|
|
519 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference
|
|
520 |
ELSE -- propagate from matched label
|
|
521 |
new.canon_label_id = (
|
|
522 |
SELECT canon_label_id
|
|
523 |
FROM taxonlabel
|
|
524 |
WHERE taxonlabel_id = new.matched_label_id
|
|
525 |
);
|
|
526 |
END IF;
|
|
527 |
|
|
528 |
-- Update canon_label_id on labels that resolve to this label
|
|
529 |
UPDATE taxonlabel
|
|
530 |
SET canon_label_id = new.canon_label_id
|
|
531 |
WHERE matched_label_id = new.taxonlabel_id
|
|
532 |
AND taxonlabel_id != new.taxonlabel_id -- avoid infinite recursion
|
|
533 |
;
|
|
534 |
ELSE -- no matched taxonlabel
|
| 504 |
535 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference
|
| 505 |
|
ELSE -- propagate from matched label
|
| 506 |
|
new.canon_label_id = (
|
| 507 |
|
SELECT canon_label_id
|
| 508 |
|
FROM taxonlabel
|
| 509 |
|
WHERE taxonlabel_id = new.matched_label_id
|
| 510 |
|
);
|
| 511 |
536 |
END IF;
|
| 512 |
|
|
| 513 |
|
-- Update canon_label_id on labels that resolve to this label
|
| 514 |
|
UPDATE taxonlabel
|
| 515 |
|
SET canon_label_id = new.canon_label_id
|
| 516 |
|
WHERE matched_label_id = new.taxonlabel_id
|
| 517 |
|
AND taxonlabel_id != new.taxonlabel_id -- avoid infinite recursion
|
| 518 |
|
;
|
| 519 |
|
ELSE -- no matched taxonlabel
|
| 520 |
|
new.canon_label_id = new.taxonlabel_id; -- make self-reference
|
| 521 |
537 |
END IF;
|
| 522 |
538 |
RETURN new;
|
| 523 |
539 |
END;
|
| ... | ... | |
| 5002 |
5018 |
|
| 5003 |
5019 |
|
| 5004 |
5020 |
--
|
| 5005 |
|
-- Name: taxonlabel_2_set_canon_label_id; Type: TRIGGER; Schema: public; Owner: -
|
|
5021 |
-- Name: taxonlabel_2_set_canon_label_id_on_insert; Type: TRIGGER; Schema: public; Owner: -
|
| 5006 |
5022 |
--
|
| 5007 |
5023 |
|
| 5008 |
|
CREATE TRIGGER taxonlabel_2_set_canon_label_id BEFORE INSERT OR UPDATE ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_set_canon_label_id();
|
|
5024 |
CREATE TRIGGER taxonlabel_2_set_canon_label_id_on_insert BEFORE INSERT ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_set_canon_label_id_on_insert();
|
| 5009 |
5025 |
|
| 5010 |
5026 |
|
| 5011 |
5027 |
--
|
|
5028 |
-- Name: taxonlabel_2_set_canon_label_id_on_update; Type: TRIGGER; Schema: public; Owner: -
|
|
5029 |
--
|
|
5030 |
|
|
5031 |
CREATE TRIGGER taxonlabel_2_set_canon_label_id_on_update BEFORE UPDATE ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_set_canon_label_id_on_update();
|
|
5032 |
|
|
5033 |
|
|
5034 |
--
|
| 5012 |
5035 |
-- Name: taxonlabel_3_parent_id_avoid_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
| 5013 |
5036 |
--
|
| 5014 |
5037 |
|
schemas/vegbien.sql: taxonlabel: taxonlabel_2_set_canon_label_id(): Only run if matched_label_id has actually changed, to avoid infinite recursion when updating canon_label_id on labels that resolve to this label when there are cycles in the data