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
Anyone knows how to solve it?