aaron.clauson@xxxxxxxxx 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
-------
