Обсуждение: 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