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