Cast record as text
Hi,
I'm trying to build an audit system for several tables. My idea was to use
triggers and plpgsql to record changes made to "important tables" to a
special audit table. My problem is that I don't want to create a separate
audit log table for each table that is being monitored. What I would like
to do is just cast the data from NEW.* or OLD.* to text and insert it into
a text column. Is this possible? I'm using version 8.1.5.
Example:
CREATE TABLE t1 (foo text, bar text);
CREATE TABLE t2 (id int, col timestamp);
CREATE TABLE audit (id int, optype char, time timestamp, user text, target
text, oldvalues text, newvalues text);
CREATE FUNCTION audit() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO audit(optype,time,user,target,newvalues) VALUES ('I ',
now(), current_user, TG_RELNAME, NEW.*::text);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
CREATE TRIGGER t1_audit AFTER INSERT OR UPDATE OR DELETE ON t1 FOR EACH
ROW EXECUTE PROCEDURE audit();
CREATE TRIGGER t2_audit AFTER INSERT OR UPDATE OR DELETE ON t2 FOR EACH
ROW EXECUTE PROCEDURE audit();
I guess the explanation is a bit vague, but I hope you got my point!
Regards
MP
am Wed, dem 14.02.2007, um 16:38:27 +0200 mailte dun@haisuli.net folgendes:
Hi,
I'm trying to build an audit system for several tables. My idea was to use
triggers and plpgsql to record changes made to "important tables" to a
special audit table. My problem is that I don't want to create a separate
audit log table for each table that is being monitored. What I would like
to do is just cast the data from NEW.* or OLD.* to text and insert it into
a text column. Is this possible? I'm using version 8.1.5.
Why do you want to reinvent the wheel?
http://pgfoundry.org/projects/tablelog/
But it use a separate log-table per table.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Why do you want to reinvent the wheel?
http://pgfoundry.org/projects/tablelog/
But it use a separate log-table per table.
Andreas
My original idea was to log changes from different tables to one audit
table, and I think tablelog uses separate audit tables for each monitored
table?
Regards
MP
am Wed, dem 14.02.2007, um 22:37:36 +0200 mailte Mikko Partio folgendes:
Why do you want to reinvent the wheel?
http://pgfoundry.org/projects/tablelog/
But it use a separate log-table per table.
Andreas
My original idea was to log changes from different tables to one audit
table, and I think tablelog uses separate audit tables for each monitored
table?
Yes, but with tablelog it is possible to restore any changes, you can
restore a table.
A blog-entry from Andreas Scherbaum, the maintainer, about tablelog:
http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote:
My original idea was to log changes from different tables to one audit
table, and I think tablelog uses separate audit tables for each monitored
table?Yes, but with tablelog it is possible to restore any changes, you can
restore a table.A blog-entry from Andreas Scherbaum, the maintainer, about tablelog:
http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
I agree that the ability to restore changes is quite nice, but my
primary goal is to record changes from many tables into one table, and I
think tablelog does not offer that. Do you know any way of casting a
record to text, or perhaps a different way altogether to audit to one
table? It's hard to believe I am the first person to come up to this
problem.
Regards
MP
Mikko Partio wrote:
I agree that the ability to restore changes is quite nice, but my
primary goal is to record changes from many tables into one table, and
I think tablelog does not offer that. Do you know any way of casting a
record to text, or perhaps a different way altogether to audit to one
table? It's hard to believe I am the first person to come up to this
problem.Regards
MP
Got it solved with pl/perl, guess pl/pgsql was the wrong choice of
language for a dynamic thing such as this.
Regards
MP