2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Scott McKellar <scott@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
20 CREATE OR REPLACE FUNCTION acq.create_acq_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
23 CREATE SEQUENCE acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
27 $creator$ LANGUAGE 'plpgsql';
29 CREATE OR REPLACE FUNCTION acq.create_acq_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
32 CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history (
33 audit_id BIGINT PRIMARY KEY,
34 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
35 audit_action TEXT NOT NULL,
36 LIKE $$ || sch || $$.$$ || tbl || $$
41 $creator$ LANGUAGE 'plpgsql';
43 CREATE OR REPLACE FUNCTION acq.create_acq_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
46 CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func ()
47 RETURNS TRIGGER AS $func$
49 INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history
50 SELECT nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
56 $func$ LANGUAGE 'plpgsql';
60 $creator$ LANGUAGE 'plpgsql';
62 CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
65 CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
66 AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
67 EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func ();
71 $creator$ LANGUAGE 'plpgsql';
73 CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
76 CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
77 SELECT -1, now() as audit_time, '-' as audit_action, *
78 FROM $$ || sch || $$.$$ || tbl || $$
81 FROM acq.$$ || sch || $$_$$ || tbl || $$_history;
85 $creator$ LANGUAGE 'plpgsql';
90 CREATE OR REPLACE FUNCTION acq.create_acq_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
92 PERFORM acq.create_acq_seq(sch, tbl);
93 PERFORM acq.create_acq_history(sch, tbl);
94 PERFORM acq.create_acq_func(sch, tbl);
95 PERFORM acq.create_acq_update_trigger(sch, tbl);
96 PERFORM acq.create_acq_lifecycle(sch, tbl);
99 $creator$ LANGUAGE 'plpgsql';
101 SELECT acq.create_acq_auditor ( 'acq', 'purchase_order' );
102 CREATE INDEX acq_po_hist_id_idx ON acq.acq_purchase_order_history( id );
104 SELECT acq.create_acq_auditor ( 'acq', 'lineitem' );
105 CREATE INDEX acq_lineitem_hist_id_idx ON acq.acq_lineitem_history( id );