Trigger to update foreign table

Question

I have two similar tables (sections) in two different databases (QGIS cloud database and AWS PostgreSQL database). I update the QGIS Cloud database (so that everyone in my organization can see the changes) on a weekly basis (uploading again the data) , but this is time consuming and I'd much rather either a direct connection with my AWS database or a way to update directly the tables in QGIS cloud when I make changes in the original AWS database.

I tried to create a foreign data wrapper in QGIS Cloud so that my maps can drink directly from my aws database, but the extension is not enabled for this online web mapping tool. Alternatively, I created a foreign data wrapper for QGIS Cloud data in my AWS database in hopes to update it with a few queries and it only seems to work perfectly when updating features without any join via QGIS or pgAdmin . For instance:

UPDATE network_data_qgis_cloud_foreign.sections
    SET contractor = 'EXAMPLE'
    WHERE id = 1 

I then tried to update the sections table in my QGIS Cloud foreign data table based on my AWS table for which I used the following code:

UPDATE network_data_qgis_cloud_foreign.sections
    SET 
    wkb_geometry = b.geom,
    fid = b.fid,
    section = b.section,
    service_points = b.service_points,
    name = b.name,
    live_premises = b.live_premises,
    off_premises = b.off_premises,
    pending_premises = b.pending_premises,
    contractor = b.contractor
    FROM network_data_qgis_cloud_foreign.sections a, network_data.sections b WHERE a.id = b.id

This query doesn't seem to update the table on my QGIS Cloud database (it just states waiting for the query to complete (but never completes). Does someone know why it doesn't seem to work?

I then thought I could create a trigger for the sections table in my AWS table to update the foreign table, but it seems to be more complex than I thought, since I am only used to creating triggers for tables in the same schema plus if I am not wrong I would need a AFTER INSERT OR UPDATE OR DELETE trigger. How can I build this function and trigger?

---

So I have tried the following piece of code:

CREATE or replace FUNCTION audit_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF TG_OP = 'INSERT' THEN
      INSERT INTO network_data_qgis_cloud_foreign.sections(id, wkb_geometry, fid,section,status,service_points,name,live_premises,off_premises,pending_premises,contractor,class)
           SELECT NEW.id, NEW.geom, NEW.fid,NEW.section,NEW.status,NEW.service_points,NEW.name,NEW.live_premises,NEW.off_premises,NEW.pending_premises,NEW.contractor,NEW.class;
      RETURN new;
    ELSIF TG_OP = 'UPDATE' THEN
      INSERT INTO network_data_qgis_cloud_foreign.sections(wkb_geometry, fid,section,status,service_points,name,live_premises,off_premises,pending_premises,contractor,class)
           SELECT NEW.geom, NEW.fid,NEW.section,NEW.status,NEW.service_points,NEW.name,NEW.live_premises,NEW.off_premises,NEW.pending_premises,NEW.contractor,NEW.class;
      RETURN new;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE
        FROM    network_data_qgis_cloud_foreign.sections AS c
        WHERE   c.id = OLD.id;
        RETURN NULL;
    END IF;
  END;
  $$;
  
  CREATE TRIGGER audit_kick
      AFTER INSERT OR UPDATE OR DELETE on network_data.sections
      FOR EACH ROW
      EXECUTE PROCEDURE audit_trigger();

And seems to be working but only inserting and deleting features. It seems to fail to update features, receiving the following response from qgis

PostGIS error while changing attributes: ERROR: prepared statement "updatefeature" already exists

Anyone knows how to solve it?

Answer 1

So I solved it with the following code, since updating features were not working in the first instance I have created a delete / insert sentence:

CREATE or replace FUNCTION audit_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF TG_OP = 'INSERT' THEN
      INSERT INTO network_data_qgis_cloud_foreign.sections(id, wkb_geometry, fid,section,status,service_points,name,live_premises,off_premises,pending_premises,contractor,class)
           SELECT NEW.id, NEW.geom, NEW.fid,NEW.section,NEW.status,NEW.service_points,NEW.name,NEW.live_premises,NEW.off_premises,NEW.pending_premises,NEW.contractor,NEW.class;
      RETURN new;
    ELSIF TG_OP = 'UPDATE' THEN
        DELETE
        FROM    network_data_qgis_cloud_foreign.sections AS c
        WHERE   c.id = OLD.id;
        INSERT INTO network_data_qgis_cloud_foreign.sections(id, wkb_geometry, fid,section,status,service_points,name,live_premises,off_premises,pending_premises,contractor,class)
           SELECT NEW.id, NEW.geom, NEW.fid,NEW.section,NEW.status,NEW.service_points,NEW.name,NEW.live_premises,NEW.off_premises,NEW.pending_premises,NEW.contractor,NEW.class;
      RETURN new;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE
        FROM    network_data_qgis_cloud_foreign.sections AS c
        WHERE   c.id = OLD.id;
        RETURN NULL;
    END IF;
  END;
  $$;

Now QGIS cloud automatically refreshes when I modify anything on my AWS database.