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
    -- Add COALESCE() to enable using plantname_unique index for lookup
44
    WHERE COALESCE(parent_id, 2147483647) = new.plantname_id
45
    ;
46
    
47
    /* Note: We don't need an ON DELETE trigger to update the descendants'
48
    ancestors when a node is deleted, because the plantname.plantname_parent_id
49
    foreign key is set to ON DELETE CASCADE, which just removes all the
50
    descendants anyway. */
51
    
52
    RETURN new;
53
END;
54
$BODY$
55
  LANGUAGE plpgsql VOLATILE
56
  COST 100;
57
ALTER FUNCTION plantname_update_ancestors()
58
  OWNER TO bien;
59

    
60
----- Ancestors table
61

    
62
-- Table: plantname_ancestor
63

    
64
-- DROP TABLE plantname_ancestor;
65

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

    
84
----- Trigger
85

    
86
-- Trigger: plantname_update_ancestors on plantname
87

    
88
-- DROP TRIGGER plantname_update_ancestors ON plantname;
89

    
90
CREATE TRIGGER plantname_update_ancestors
91
  AFTER INSERT OR UPDATE
92
  ON plantname
93
  FOR EACH ROW
94
  EXECUTE PROCEDURE plantname_update_ancestors();
(10-10/20)