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