plpgsql replication stored procedure

From: aaron(dot)clauson(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql replication stored procedure
Date: 2006-04-17 13:58:58
Message-ID: 1145282338.790460.109290@e56g2000cwe.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-04-17 14:18:51 Re: hard shutdown of system
Previous Message Devrim GUNDUZ 2006-04-17 12:50:35 Re: Question about partitioning