1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
--
-- table_log () -- log changes to another table
--
--
-- see README.md for details
--
--
-- written by Andreas ' ads' Scherbaum (ads@pgug.de)
--
--
-- drop old trigger
DROP TRIGGER test_log_chg ON test; -- ignore any error
-- create demo table
DROP TABLE test; -- ignore any error
CREATE TABLE test (
id INT NOT NULL
PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
-- create the table without data from demo table
DROP TABLE test_log; -- ignore any error
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
CREATE SEQUENCE test_log_id;
SELECT SETVAL('test_log_id', 1, FALSE);
ALTER TABLE test_log ALTER COLUMN trigger_id SET DEFAULT NEXTVAL('test_log_id');
-- create function
CREATE FUNCTION table_log ()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
-- create trigger
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
EXECUTE PROCEDURE table_log();
-- test trigger
INSERT INTO test VALUES (1, 'name');
SELECT * FROM test;
SELECT * FROM test_log;
UPDATE test SET name='other name' WHERE id=1;
SELECT * FROM test;
SELECT * FROM test_log;
-- create restore table
SELECT table_log_restore_table('test', 'id', 'test_log', 'trigger_id', 'test_recover', NOW());
SELECT * FROM test_recover;
DROP TABLE test_log;
DROP TABLE test;
|
