X3dYfiHN

1CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
2    DECLARE action_name text;
3    BEGIN
4        IF TG_OP = 'DELETE' THEN
5            OLD.timestamp := current_timestamp;
6            action_name := 'deleted';
7            INSERT INTO dev_hist SELECT action_name, user, OLD.*;
8        ELSE
9            NEW.timestamp := current_timestamp;
10            IF (TG_OP = 'INSERT') THEN
11                action_name := 'added';
12            ELSIF (TG_OP = 'UPDATE') THEN
13                action_name := 'modified';
14            END IF;
15            INSERT INTO dev_hist SELECT action_name, user, NEW.*;
16        END IF;
17        RETURN NULL; -- result is ignored since this is an AFTER trigger
18    END;
19$dev_hist$ LANGUAGE plpgsql;
20
21DROP TRIGGER IF EXISTS dev_hist ON dev;
22CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON dev
23    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist(); 
24     
25CREATE TABLE T (id int, name text);
26
27CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
28    DECLARE action_name text;
29    BEGIN
30
31        -- add your catalog information.
32        CREATE TABLE IF NOT EXISTS dev_hist
33        (
34            action text
35        );
36
37        IF TG_OP = 'DELETE' THEN
38            action_name := 'deleted';
39            INSERT INTO dev_hist SELECT action_name;
40        ELSE
41            IF (TG_OP = 'INSERT') THEN
42                action_name := 'added';
43            ELSIF (TG_OP = 'UPDATE') THEN
44                action_name := 'modified';
45            END IF;
46            INSERT INTO dev_hist SELECT action_name;
47        END IF;
48        RETURN NULL; -- result is ignored since this is an AFTER trigger
49    END;
50$dev_hist$ LANGUAGE plpgsql;
51
52DROP TRIGGER IF EXISTS dev_hist ON T;
53CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON T
54    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist(); 
55     
56INSERT INTO T VALUES (1);
57DELETE FROM T WHERE id = 1; 
58     
59SELECT * FROM dev_hist; 
60     
61SELECT * FROM dev_hist; 
62     
63dev_hist 
64     
65CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
66    DECLARE action_name text;
67    DECLARE rec record;
68    BEGIN
69        IF TG_OP = 'DELETE' THEN
70            OLD.timestamp := current_timestamp;
71            action_name := 'deleted';
72            --INSERT INTO dev_hist SELECT action_name, user, OLD.*;
73            rec := OLD;
74        ELSE
75            NEW.timestamp := current_timestamp;
76            IF (TG_OP = 'INSERT') THEN
77                action_name := 'added';
78            ELSIF (TG_OP = 'UPDATE') THEN
79                action_name := 'modified';
80            END IF;
81            --INSERT INTO dev_hist SELECT action_name, user, NEW.*;
82            rec := NEW;
83        END IF;
84
85        IF NOT EXISTS(SELECT *
86                      FROM   INFORMATION_SCHEMA.TABLES
87                      WHERE  table_name = 'dev_hist') THEN
88            RAISE NOTICE 'creating table';
89            CREATE TABLE dev_hist AS
90                SELECT action_name, user, rec.*;
91        ELSE
92            RAISE NOTICE 'table exists';
93            INSERT INTO dev_hist SELECT action_name, user, rec.*;
94        END IF;
95        RETURN NULL; -- result is ignored since this is an AFTER trigger
96    END;
97$verify$ LANGUAGE plpgsql; 
98     
99NOTICE:  creating table
100NOTICE:  table exists
101NOTICE:  table exists
102NOTICE:  table exists
103NOTICE:  table exists
104NOTICE:  table exists
105INSERT 0 1
106
107Query returned successfully in 69 msec.