/schemas/tree_cross-links.sql - Annotate - BIEN 3 - NCEAS Projects

root/schemas/tree_cross-links.sql @ 5632

-            aaronmk
+/* To get the place trigger, replace taxonconcept with place and remove
 references to matched_concept_id. */
             aaronmk
-            aaronmk
+----- Functions
             aaronmk
-            aaronmk
+CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors(new taxonconcept, old_parent_id integer DEFAULT NULL::integer)
   RETURNS void AS
-            aaronmk
+$BODY$
-            aaronmk
+DECLARE
     -- Use matched_concept_id's ancestors instead if available
-            aaronmk
+    parent_id_ taxonconcept.taxonconcept_id%TYPE := COALESCE(
         NULLIF(new.matched_concept_id, new.taxonconcept_id), new.parent_id);
-            aaronmk
+BEGIN
-            aaronmk
+    IF parent_id_ IS DISTINCT FROM old_parent_id THEN
         DECLARE
             -- These include the parent itself
             old_ancestors integer[] := (
                 SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
                 WHERE taxonconcept_id = old_parent_id
             );
             new_ancestors integer[] := (
                 SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
                 WHERE taxonconcept_id = parent_id_
             );
             descendant_id integer;
         BEGIN
             FOR descendant_id IN -- also includes self
                 SELECT taxonconcept_id
                 FROM taxonconcept_ancestor
                 WHERE ancestor_id = new.taxonconcept_id
             LOOP
                 -- Delete old parent's ancestors
                 DELETE FROM taxonconcept_ancestor
                 WHERE taxonconcept_id = descendant_id
                 AND ancestor_id = ANY (old_ancestors)
+                ;
                 -- Add new parent's ancestors
                 INSERT INTO taxonconcept_ancestor
                 (taxonconcept_id, ancestor_id)
                 SELECT descendant_id, unnest(new_ancestors)
+                ;
             END LOOP;
         END;
-            aaronmk
+    END IF;
             aaronmk
-            aaronmk
+    /* Note: We don't need an ON DELETE trigger to update the descendants'
-            aaronmk
+    ancestors when a node is deleted, because the
     taxonconcept_ancestor.ancestor_id foreign key is ON DELETE CASCADE. */
-            aaronmk
+END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_insert()
   RETURNS trigger AS
 $BODY$
 BEGIN
     /* Add "ancestor" for this node. This is useful for queries, because you
     don't have to separately test if the leaf node is the one you're looking
     for, in addition to that leaf node's ancestors. */
-            aaronmk
+    INSERT
-            aaronmk
+    INTO taxonconcept_ancestor
     (taxonconcept_id, ancestor_id)
     VALUES (new.taxonconcept_id, new.taxonconcept_id)
             aaronmk
-            aaronmk
+    PERFORM taxonconcept_update_ancestors(new);
             aaronmk
-            aaronmk
+    RETURN new;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_update()
   RETURNS trigger AS
 $BODY$
 BEGIN
-            aaronmk
+    PERFORM taxonconcept_update_ancestors(new, COALESCE(
         NULLIF(old.matched_concept_id, old.taxonconcept_id), old.parent_id));
             aaronmk
-            aaronmk
+    RETURN new;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ----- Ancestors table
-            aaronmk
+CREATE TABLE taxonconcept_ancestor
             aaronmk
-            aaronmk
+  taxonconcept_id integer NOT NULL,
-            aaronmk
+  ancestor_id integer NOT NULL,
-            aaronmk
+  CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ),
   CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)
       REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)
       REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE
+)
 WITH (
   OIDS=FALSE
 );
-            aaronmk
+COMMENT ON TABLE taxonconcept_ancestor
   IS 'Stores the accepted ancestors of a taxonconcept. Auto-populated, so should not be manually modified.';
             aaronmk
-            aaronmk
+CREATE INDEX taxonconcept_ancestor_descendants
   ON taxonconcept_ancestor
   USING btree
   (ancestor_id , taxonconcept_id );
             aaronmk
-            aaronmk
+----- Triggers
 CREATE TRIGGER taxonconcept_update_ancestors_on_insert
   AFTER INSERT
-            aaronmk
+  ON taxonconcept
-            aaronmk
+  FOR EACH ROW
-            aaronmk
+  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();
 CREATE TRIGGER taxonconcept_update_ancestors_on_update
   AFTER UPDATE
   ON taxonconcept
   FOR EACH ROW
   EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update();

Project

General

Profile