Project

General

Profile

1
/* To get the namedplace trigger, just search and replace "plantname" with
2
"namedplace". */
3

    
4
----- Trigger function
5

    
6
-- Function: plantname_update_ancestors()
7

    
8
-- DROP FUNCTION plantname_update_ancestors();
9

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

    
59
----- Ancestors table
60

    
61
-- Table: plantname_ancestor
62

    
63
-- DROP TABLE plantname_ancestor;
64

    
65
CREATE TABLE plantname_ancestor
66
(
67
  plantname_id integer NOT NULL,
68
  ancestor_id integer NOT NULL,
69
  CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id , ancestor_id ),
70
  CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id)
71
      REFERENCES plantname (plantname_id) MATCH SIMPLE
72
      ON UPDATE CASCADE ON DELETE CASCADE,
73
  CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id)
74
      REFERENCES plantname (plantname_id) MATCH SIMPLE
75
      ON UPDATE CASCADE ON DELETE CASCADE
76
)
77
WITH (
78
  OIDS=FALSE
79
);
80
ALTER TABLE plantname_ancestor
81
  OWNER TO bien;
82

    
83
-- Index: fki_plantname_ancestor_ancestor_id
84

    
85
-- DROP INDEX fki_plantname_ancestor_ancestor_id;
86

    
87
CREATE INDEX fki_plantname_ancestor_ancestor_id
88
  ON plantname_ancestor
89
  USING btree
90
  (ancestor_id );
91

    
92
-- Index: fki_plantname_ancestor_plantname_id
93

    
94
-- DROP INDEX fki_plantname_ancestor_plantname_id;
95

    
96
CREATE INDEX fki_plantname_ancestor_plantname_id
97
  ON plantname_ancestor
98
  USING btree
99
  (plantname_id );
100

    
101
----- Trigger
102

    
103
-- Trigger: plantname_update_ancestors on plantname
104

    
105
-- DROP TRIGGER plantname_update_ancestors ON plantname;
106

    
107
CREATE TRIGGER plantname_update_ancestors
108
  AFTER INSERT OR UPDATE
109
  ON plantname
110
  FOR EACH ROW
111
  EXECUTE PROCEDURE plantname_update_ancestors();
(7-7/18)