Обсуждение: Simple row serialization?

Hi,

I'd like to implement some simple data logging via triggers on a small
number of infrequently updated tables and I'm wondering if there are
some helpful functions, plugins or idioms that would serialize a row
(received for example in a AFTER INSERT trigger) into a string that I'd
store in the log table. There's a limited number of field types
involved: varchars, integers and booleans. I'm not looking for anything
fancy, comma-separated string result would be just fine; even better,
something like a dictionary ("field_name":"field_value",...) would be
nice. The reason for trying to do it this way is that want to have a
single log table to log many tables (again, they are infrequently
updated). I need this for PostgreSQL 8.1.

I got suggestions to try composite types but I don't think they could be
useful for this. What I need is possibly a generic "row" type ("any" and
"record" generate syntax error in CREATE TABLE) - any ideas on where to
start looking?

> I'd like to implement some simple data logging via triggers on a small
> number of infrequently updated tables and I'm wondering if there are
> some helpful functions, plugins or idioms that would serialize a row


If you're familiar with perl, you can try PL/Perl.

http://www.postgresql.org/docs/8.2/interactive/plperl-triggers.html

Here's an example (untested).  If you're using quotes and colons as delimeters,
you may also need to escape those in your data.



CREATE OR REPLACE FUNCTION log_change() RETURNS trigger AS $$
    my ($old_serialized, $new_serialized);

    foreach my $col (keys %{$_TD->{old}}) {
        $old_serialized .= "'" . $col ."':'" . $_TD->{old}{$col} . "',";
    }
    foreach my $col (keys %{$_TD->{new}}) {
        $new_serialized .= "'" . $col ."':'" . $_TD->{new}{$col} . "',";
    }

    my $qry = spi_prepare('insert into log_tbl values ($1,$2)', VARCHAR, VARCHAR);
    spi_exec_prepared($qry, $old_serialized, $new_serialized);
    spi_freeplan($qry);

    return;
$$ LANGUAGE plperl;

Adam Rich wrote:
>> I'd like to implement some simple data logging via triggers on a small
>> number of infrequently updated tables and I'm wondering if there are
>> some helpful functions, plugins or idioms that would serialize a row
>
>
> If you're familiar with perl, you can try PL/Perl.

Thanks, but another solution has been suggested to me, much simpler:

create or replace function data_log() returns trigger as $$
         declare
                 sdata   text;
         begin
                 sdata = new;
                 insert into log(data) values (sdata);
                 return NULL;
         end;
$$ language plpgsql;

create trigger data_insert after insert on data
     for each row execute procedure data_log();

(from idea by Tom Lane)