Re: Simple row serialization?

["Adam Rich" <adam.r@xxxxxxxxxxxxx>]

> 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;




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/