Re: [GENERAL] Cast record as text SOLVED
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cast record as text
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Cast record as text
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cast record as text
> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Cast record as text
am Wed, dem 14.02.2007, um 16:38:27 +0200 mailte [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
