Hi Tom, You are probably right that the performance will become an issue. I do have a working solution using plpgsql, though, so I will at least try it out for a while.
For anyone who is interested, I created a template file (using the perl module Template.pm syntax) that works with the perl module SQL::Translator to examine my ddl file and create from it the audit tables and the functions and triggers to make them work. The template file copied below, and SQL::Translator is available from CPAN and from http://sqlfairy.sourceforge.net/ . Thanks, Scott ---------------------------------------------- --audit tables generated from -- % sqlt -f PostgreSQL -t TTSchema --template add-audits.tmpl nofuncs.sql > \ -- audits.sql [% FOREACH table IN schema.get_tables %] DROP TABLE audit_[% table.name %]; CREATE TABLE audit_[% table.name %] ( [% FOREACH field IN table.get_fields %] [% field.name %] [% IF field.data_type == 'serial'; 'int'; ELSE; field.data_type; END %][% IF field.size AND (field.data_type == 'char' OR field.data_type == 'varchar') %]([% field.size.join(', ') %])[% END %], [% END %] transaction_date timestamp not null default now(), transaction_type char(1) not null ); GRANT ALL on audit_[% table.name %] to PUBLIC; CREATE OR REPLACE FUNCTION audit_update_delete_[% table.name %]() RETURNS trigger AS ' DECLARE [% FOREACH field IN table.get_fields %][% field.name %]_var [% IF field.data_type == 'serial'; 'int'; ELSE; field.data_type; END %][% IF field.size AND (field.data_type == 'char' OR field.data_type == 'varchar') %]([% field.size.join(', ') %])[% END %]; [% END %] transaction_type_var char; BEGIN [% FOREACH field IN table.get_fields %][% field.name %]_var = OLD.[% field.name %]; [% END %] IF TG_OP = ''DELETE'' THEN transaction_type_var = ''D''; ELSE transaction_type_var = ''U''; END IF; INSERT INTO audit_[% table.name %] ( [% FOREACH field IN table.get_fields %] [% field.name %], [% END %] transaction_type ) VALUES ( [% FOREACH field IN table.get_fields %] [% field.name %]_var, [% END %] transaction_type_var ); IF TG_OP = ''DELETE'' THEN return null; ELSE return NEW; END IF; END ' LANGUAGE plpgsql; DROP TRIGGER [% table.name %]_audit_ud ON [% table.name %]; CREATE TRIGGER [% table.name %]_audit_ud BEFORE UPDATE OR DELETE ON [% table.name %] FOR EACH ROW EXECUTE PROCEDURE audit_update_delete_[% table.name %] (); [% END %] On Fri, 2004-10-15 at 11:02, Tom Lane wrote: > Scott Cain <[EMAIL PROTECTED]> writes: > > Heck! So much for feeling close. It is somewhat frustrating to me that > > such an obviously useful tool (having and using audit tables) should be > > so difficult to implement. > > The only really reasonable way to implement this is as a C function > anyway. I think anything involving a PL language is going to be a huge > performance drag, if you intend to put it on essentially every table. > > There are some pretty closely related examples in contrib/spi/, though > I don't see anything that does *exactly* what you want. If you came up > with something that does, I think it'd be reasonable to add it to that > set of examples ... > > regards, tom lane -- ------------------------------------------------------------------------ Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
