56DKy3x2

1
2DROP TYPE IF EXISTS user_type_enum CASCADE;
3CREATE TYPE user_type_enum AS ENUM ( 'administrator', 'person', 'agency' );
4
5DROP TABLE IF EXISTS users CASCADE;
6CREATE TABLE users (
7    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
8	type user_type_enum NOT NULL,
9    email VARCHAR NOT NULL,
10    password VARCHAR NOT NULL,
11    created TIMESTAMP DEFAULT NOW()
12);
13
14DROP TABLE IF EXISTS users_audit CASCADE;
15CREATE TABLE users_audit (
16	audit_operation CHAR( 1 ) NOT NULL,
17	audit_timestamp timestamp NOT NULL,
18    id INT NOT NULL,
19	type user_type_enum NOT NULL,
20    email VARCHAR NOT NULL,
21    password VARCHAR NOT NULL,
22    created TIMESTAMP NOT NULL
23);
24
25CREATE OR REPLACE FUNCTION process_users_audit() RETURNS TRIGGER AS $users_audit$
26    BEGIN
27        IF ( TG_OP = 'DELETE' ) THEN
28            INSERT INTO users_audit SELECT 'D', now(), OLD.*;
29        ELSIF ( TG_OP = 'UPDATE' ) THEN
30            INSERT INTO users_audit SELECT 'U', now(), NEW.*;
31        ELSIF ( TG_OP = 'INSERT' ) THEN
32            INSERT INTO users_audit SELECT 'I', now(), NEW.*;
33        END IF;
34        RETURN NULL;
35    END;
36$users_audit$ LANGUAGE plpgsql;
37
38CREATE TRIGGER users_audit
39AFTER INSERT OR UPDATE OR DELETE ON users
40FOR EACH ROW EXECUTE PROCEDURE process_users_audit();
41
42CREATE OR REPLACE FUNCTION process_users_audit_maintaince() RETURNS TRIGGER AS $users_audit_maintaince$
43    BEGIN
44        DELETE FROM users_audit WHERE DATE_PART( 'day', NOW() - audit_timestamp ) >= 90;
45        RETURN NULL;
46    END;
47$users_audit_maintaince$ LANGUAGE plpgsql;
48
49CREATE TRIGGER users_audit_maintaince
50AFTER INSERT ON users_audit
51FOR EACH ROW EXECUTE PROCEDURE users_audit_maintaince();
52