For perl/database geeks...

[rasilon_x]

A function to replicate arbitrary postgres table operations to other databases (any sensible database supported by DBI).



Since I'm making this public -- License: GPL or BSD with attribution.

CREATE OR REPLACE FUNCTION replicate() RETURNS trigger AS $$
elog(NOTICE,"Replication Handler");

my $q;
my @vals=();
if($_TD->{event} eq "INSERT"){
elog(NOTICE,"Handling Insert");
my $qp = "insert into ".$_TD->{relname}."(";
my $qv = ") values (";
foreach $col (sort(keys(%{$_TD->{new}}))){
$qp = $qp."$col,";
$qv = $qv."?,";
push(@vals,$_TD->{new}{$col});
}
chop($qp);
chop($qv);
$q = $qp.$qv.");";
}elsif($_TD->{event} eq "UPDATE"){
elog(NOTICE,"Handling Update");
$q = "update ".$_TD->{relname}." set ";
foreach $col (sort(keys(%{$_TD->{new}}))){
$q = $q."$col = ?,";
push(@vals,$_TD->{new}{$col});
}
chop($q);
$q = $q." where ";
foreach $col (sort(keys(%{$_TD->{old}}))){
$q = $q."$col = ? and ";
push(@vals,$_TD->{old}{$col});
}
$q =~ s/ and $//;
$q = $q.";";

}elsif($_TD->{event} eq "DELETE"){
elog(NOTICE,"Handling Delete");
$q = "delete from ".$_TD->{relname}." where ";
foreach $col (sort(keys(%{$_TD->{old}}))){
$q = $q."$col = ? and ";
push(@vals,$_TD->{old}{$col});
}
$q =~ s/ and $//;
$q = $q.";";

}else{
elog(NOTICE,"Unhandled Because I don't know what it was...");
return;
}
elog(NOTICE,$q);

use DBI;
$rv = spi_exec_query("select * from treps;");
my $nrows = $rv->{processed};
TARGET: foreach my $rn (0 .. $nrows - 1) {
my $row = $rv->{rows}[$rn];
if($row->{rep_url} =~ /^DBI/){
elog(NOTICE,"DBI rep");
my $dbh;
eval{
$dbh = DBI->connect($row->{rep_url},
$row->{rep_uname},
$row->{rep_pwd},
{RaiseError => 1, PrintError => 1, AutoCommit => 1 }
);
};
if($@){
elog(NOTICE,"Connecting to ".$row->{rep_url}." failed. ".$@);
next TARGET;
}
elog(NOTICE,"connected to ".$row->{rep_url});

my $sth;
eval{
$sth = $dbh->prepare($q);
};
if($@){
elog(NOTICE,"Preparing $q failed when connected to ".$row->{rep_url}.". ".$@);
$dbh->disconnect();
next TARGET;
}

eval{
$sth->execute(@vals);
};
if($@){
elog(NOTICE,"Executing $q failed when connected to ".$row->{rep_url}.". ".$@);
$dbh->disconnect();
next TARGET;
}

$dbh->disconnect();

}else{
elog(NOTICE,"Unknown Replication target.");
}
}
return;
$$ language plperlu;