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
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 |