Re: plpgsql replication stored procedure

[Peter Wilson <petew(at)yellowhawk(dot)co(dot)uk>]

aaron(dot)clauson(at)gmail(dot)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
-------