Postgres Pro
Downloads
Home   >   mailing lists

Re: Support logical replication of DDLs - Mailing list pgsql-hackers

From Marcos Pegoraro
Subject Re: Support logical replication of DDLs
Date
Msg-id CAB-JLwYkc6T4PX1OD3r9Zm-39OVpZ5+Ev4dvLYPkMT2EJEgyEA@mail.gmail.com
Whole thread Raw
In response to Support logical replication of DDLs  (Zheng Li <zhengli10@gmail.com>)
List pgsql-hackers
Tree view
Em seg., 21 de fev. de 2022 às 13:13, Zheng Li <zhengli10@gmail.com> escreveu:

2. Table level
Allows DDLs on the published tables to be replicated except for
certain edge cases.

Think how to handle triggers and functions with same name but different purpose.

Publisher
create function public.audit() returns trigger language plpgsql as $$
begin
  new.Audit_User = current_user();
  new.Audit_Date_Time = now();
  return new;
end;$$
create trigger audit before insert or update on foo for each row execute procedure public.audit();

Subscriber
create function public.audit() returns trigger language plpgsql as $$
begin
  insert into Audit(Audit_Date_Time, Audit_User, Schema_Name, Table_Name, Audit_Action, Field_Values) values(new.Audit_ts, new.Audit_User, tg_table_schema, tg_table_name, tg_op, row_to_json(case when tg_op = 'DELETE' then old.* else new.* end));
  return null;
end;$$
create trigger audit after insert or update or delete on foo for each row execute procedure public.audit();

regards,
Marcos
 

pgsql-hackers by date:

Previous
From: Aleksander Alekseev
Date:
Subject: Re: Support logical replication of DDLs
Next
From: "osumi.takamichi@fujitsu.com"
Date:
Subject: RE: Design of pg_stat_subscription_workers vs pgstats