I'm trying to create a trigger inside of a Postgres RDS database hosted in Frankfurt that we will call "Frankfurt". That trigger will poll tables in a distant Postgres RDS database hosted in Seoul, let's call that database "Seoul".
The Seoul database will never have delete nor update. It will only have insert.
The aim of that trigger is for each insert into the Seoul database to copy the rows from Seoul that are not in Frankfurt, to the Frankfurt database.
CREATE OR REPLACE FUNCTION my_dml_function() RETURNS TRIGGER AS $employees$ BEGIN -- -- IF (TG_OP = 'INSERT') THEN perform dblink_connect('dbname=database_seoul user=postgres password=ExtraPassword'); INSERT INTO all_employees SELECT * FROM dblink('foreign_server',$RDS$ SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM all_employees) $RDS$) AS t1; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER my_dml_trigger AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE PROCEDURE my_dml_function();
I'm not sure if it is the best way.