Project

General

Profile

1
----- Trigger function
2

    
3
-- Function: plantname_update_ancestors()
4

    
5
-- DROP FUNCTION plantname_update_ancestors();
6

    
7
CREATE OR REPLACE FUNCTION plantname_update_ancestors()
8
  RETURNS trigger AS
9
$BODY$
10
BEGIN
11
    IF new.parent_id IS NOT NULL THEN
12
        -- Delete existing ancestors
13
        DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
14
        -- Copy parent's ancestors to this node's ancestors
15
        INSERT
16
        INTO plantname_ancestor
17
        (plantname_id, ancestor_id)
18
        SELECT
19
            new.plantname_id, ancestor_id
20
        FROM plantname_ancestor
21
        WHERE plantname_id = new.parent_id
22
        ;
23
        -- Add "ancestor" for this node
24
        /* This is useful for queries, because you don't have to separately test
25
        if the leaf node is the one you're looking for, in addition to that leaf
26
        node's ancestors. */
27
        INSERT
28
        INTO plantname_ancestor
29
        (plantname_id, ancestor_id)
30
        VALUES (new.plantname_id, new.plantname_id)
31
        ;
32
        -- Tell immediate children to update their ancestors lists, which will
33
        -- recursively tell all descendants
34
        UPDATE plantname
35
        SET plantname_id = plantname.plantname_id -- set to itself
36
        WHERE parent_id = plantname_id -- the plantname_id passed as param
37
        ;
38
        /*
39
        Note: We don't need an ON DELETE trigger to update the descendants'
40
        ancestors when a node is deleted, because the
41
        plantname.plantname_parent_id foreign key is set to ON DELETE CASCADE,
42
        which just removes all the descendants anyway.
43
        */
44
    END IF;
45
    RETURN new;
46
END;
47
$BODY$
48
  LANGUAGE plpgsql VOLATILE
49
  COST 100;
50
ALTER FUNCTION plantname_update_ancestors()
51
  OWNER TO bien;
52

    
53
----- Ancestors table
54

    
55
-- Table: plantname_ancestor
56

    
57
-- DROP TABLE plantname_ancestor;
58

    
59
CREATE TABLE plantname_ancestor
60
(
61
  plantname_id integer NOT NULL,
62
  ancestor_id integer NOT NULL,
63
  CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id , ancestor_id ),
64
  CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id)
65
      REFERENCES plantname (plantname_id) MATCH SIMPLE
66
      ON UPDATE CASCADE ON DELETE CASCADE,
67
  CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id)
68
      REFERENCES plantname (plantname_id) MATCH SIMPLE
69
      ON UPDATE CASCADE ON DELETE CASCADE
70
)
71
WITH (
72
  OIDS=FALSE
73
);
74
ALTER TABLE plantname_ancestor
75
  OWNER TO bien;
76

    
77
-- Index: fki_plantname_ancestor_ancestor_id
78

    
79
-- DROP INDEX fki_plantname_ancestor_ancestor_id;
80

    
81
CREATE INDEX fki_plantname_ancestor_ancestor_id
82
  ON plantname_ancestor
83
  USING btree
84
  (ancestor_id );
85

    
86
-- Index: fki_plantname_ancestor_plantname_id
87

    
88
-- DROP INDEX fki_plantname_ancestor_plantname_id;
89

    
90
CREATE INDEX fki_plantname_ancestor_plantname_id
91
  ON plantname_ancestor
92
  USING btree
93
  (plantname_id );
94

    
95
----- Trigger
96

    
97
-- Trigger: plantname_update_ancestors on plantname
98

    
99
-- DROP TRIGGER plantname_update_ancestors ON plantname;
100

    
101
CREATE TRIGGER plantname_update_ancestors
102
  AFTER INSERT OR UPDATE
103
  ON plantname
104
  FOR EACH ROW
105
  EXECUTE PROCEDURE plantname_update_ancestors();
(4-4/15)