2 * Copyright (C) 2009 Equinox Software, Inc.
5 * Released under GNU General Public License version 2
10 INSERT INTO config.upgrade_log (version) VALUES ('0087'); -- atz
12 CREATE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
15 CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
19 $creator$ LANGUAGE 'plpgsql';
21 CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
24 CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
25 audit_id BIGINT PRIMARY KEY,
26 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
27 audit_action TEXT NOT NULL,
28 LIKE $$ || sch || $$.$$ || tbl || $$
33 $creator$ LANGUAGE 'plpgsql';
35 CREATE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
38 CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
39 RETURNS TRIGGER AS $func$
41 INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history
42 SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
48 $func$ LANGUAGE 'plpgsql';
52 $creator$ LANGUAGE 'plpgsql';
54 CREATE FUNCTION auditor.create_auditor_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
57 CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
58 AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
59 EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
63 $creator$ LANGUAGE 'plpgsql';
65 CREATE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
68 CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
69 SELECT -1, now() as audit_time, '-' as audit_action, *
70 FROM $$ || sch || $$.$$ || tbl || $$
73 FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
77 $creator$ LANGUAGE 'plpgsql';
79 DROP FUNCTION IF EXISTS auditor.create_auditor (TEXT, TEXT); -- Besides this line and the 0087 INSERT, the rest of this file is 900.audit-functions.sql
83 CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
85 PERFORM auditor.create_auditor_seq(sch, tbl);
86 PERFORM auditor.create_auditor_history(sch, tbl);
87 PERFORM auditor.create_auditor_func(sch, tbl);
88 PERFORM auditor.create_auditor_update_trigger(sch, tbl);
89 PERFORM auditor.create_auditor_lifecycle(sch, tbl);
92 $creator$ LANGUAGE 'plpgsql';