plpgsql replication stored procedure

aaron.clauson@gmail.com writes:

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.
I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

There is no way to write such a generic trigger in plpgsql (and even if
you could, its performance would suck :-(). You could do it in C if
you're sufficiently determined.

regards, tom lane

On 4/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

aaron.clauson@gmail.com writes:

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.
I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

My database has a table that store all changes mades on all tables. The
insert is made by onde single trigger, but I create a function that build
functions to convert an generic record on a text value.
If this is usefull, mail-me ;-)
Dynamic record name cannot be made on a plpgsql function. This topic was
discursed some months ago.

--
William Leite Araújo
Especialista em Geoprocessamento - UFMG
Bacharel em Ciêncida da Computação - UFMG
MSN: ufmgwil@yahoo.com.br
ICQ: 222159351
GTalk: william.bh@gmail.com
Yahoo: ufmgwil@yahoo.com.br
Skype: william.bh

aaron.clauson@gmail.com wrote:

Hi,

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.

I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

The stored procedure is below and what I'm trying to do is find a way
to get the column from the NEW record that has the name
constraintColName. If I could do that I would replace NEW.oid with the
equivalent of NEW[constraintColName] and remove the need for oid's on
the table.

create or replace function replicate() returns trigger as
$$

declare
constraintName varchar;
constraintColName varchar;
keyId varchar;
slaves record;

begin
select into constraintName constraint_name from
information_schema.table_constraints where table_name = TG_RELNAME and
constraint_type = 'PRIMARY KEY';
select into constraintColName column_name from
information_schema.key_column_usage where constraint_name =
constraintName;
-- execute 'select ' || constraintColName || ' from ' || NEW into
keyId;

for slaves in
select slaveid from replicationslaves
loop
insert into replicationentries values(default, slaves.slaveid,
TG_OP, TG_RELNAME , NEW.oid, default);
end loop;
return NULL;
end;$$
language 'plpgsql';

Aaron

Why not use or adapt the 'C' function in the dbmirror implementation shipped
with Postgres? The Perl script to replicate to the slave database is very
inefficient but the trigger function itself is very fast.

I've also got a C++ implementation of the dbmirror replication perl script as
well if it's any use

Pete
--
www.whitebeam.org
www.yellowhawk.co.uk
-------