3 INSERT INTO config.upgrade_log (version) VALUES ('0140'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION acq.create_acq_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
8 CREATE SEQUENCE acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
12 $creator$ LANGUAGE 'plpgsql';
14 CREATE OR REPLACE FUNCTION acq.create_acq_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
17 CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history (
18 audit_id BIGINT PRIMARY KEY,
19 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
20 audit_action TEXT NOT NULL,
21 LIKE $$ || sch || $$.$$ || tbl || $$
26 $creator$ LANGUAGE 'plpgsql';
28 CREATE OR REPLACE FUNCTION acq.create_acq_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
31 CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func ()
32 RETURNS TRIGGER AS $func$
34 INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history
35 SELECT nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
41 $func$ LANGUAGE 'plpgsql';
45 $creator$ LANGUAGE 'plpgsql';
47 CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
50 CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
51 AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
52 EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func ();
56 $creator$ LANGUAGE 'plpgsql';
58 CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
61 CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
62 SELECT -1, now() as audit_time, '-' as audit_action, *
63 FROM $$ || sch || $$.$$ || tbl || $$
66 FROM acq.$$ || sch || $$_$$ || tbl || $$_history;
70 $creator$ LANGUAGE 'plpgsql';
75 CREATE OR REPLACE FUNCTION acq.create_acq_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
77 PERFORM acq.create_acq_seq(sch, tbl);
78 PERFORM acq.create_acq_history(sch, tbl);
79 PERFORM acq.create_acq_func(sch, tbl);
80 PERFORM acq.create_acq_update_trigger(sch, tbl);
81 PERFORM acq.create_acq_lifecycle(sch, tbl);
84 $creator$ LANGUAGE 'plpgsql';
86 SELECT acq.create_acq_auditor ( 'acq', 'purchase_order' );
87 CREATE INDEX acq_po_hist_id_idx ON acq.acq_purchase_order_history( id );
89 SELECT acq.create_acq_auditor ( 'acq', 'lineitem' );
90 CREATE INDEX acq_lineitem_hist_id_idx ON acq.acq_lineitem_history( id );