Bucardo custom replication logic

Question

I have a question regarding the Bucardo capabilities in Postgre SQL. Bucardo puts in sync tables between several databases. Imagine we have table Orders in DB1 and DB2.

create table orders(order_id integer primary key, item_id integer, quantity integer); 

And we're making changes to Orders table in DB1.

insert into orders(item_id,quantity) values(1,235);

Then Bucardo replicates all these changes to Orders table in DB2. But apart from this sync I want Bucardo to modify table Stock in DB2

create table stock(item_id integer primary key, name varchar(50), quantity integer);

Just to decrease the quantity field of stock table in DB2 by the value 235 (the value inserted to quantity field of orders table in DB1) for the record with item_id = 1. Is it possible to customize the Bucardo in this way? And what is the best way to implement this functionality?

Answer 1

This will be hard to achieve as bucardo doesn't replicate based on SQL statements. You can however use stored procedures on the primary which is DB1 and also replicate that table.

create or replace function stock_func() returns trigger as $$
DECLARE
    val int;
BEGIN
    val := NEW.quantity #do you addition or subtraction here
    #add your update statement below 
    execute 'UPDATE stock set .....;
    return NULL;
END;
$$ language plpgsql;

 create trigger update_stock before insert on address for each row execute procedure stock_func();