Using NOTIFY to Automatically Refresh Layers in QGIS

One of the most brilliant but little-known features of QGIS is the ability to trigger layer refreshes and events in response to notifications from PostgreSQL. This was developed by the wizards from Oslandia and is easily added to any existing table in your PostgreSQL database - including PostGIS tables. This feature was added in version 3.0 (see https://qgis.org/en/site/forusers/visualchangelog30/#feature-trigger-layer-refresh-or-layer-actions-from-postgresql-notify-signal).

Take for example this simple table:

emp-shared=# \d line

Table "public.line"

   Column   |           Type            |                     Modifiers                     

------------+---------------------------+---------------------------------------------------

id          | integer                   | not null default nextval('line_id_seq'::regclass)

name        | character varying(255)    | not null

voltage_kv  | real                      | not null

source_id   | integer                   | not null

geom        | geometry(LineString,4326) | 

Indexes:

  "line_pkey" PRIMARY KEY, btree (id)

  "sidx_line_geom" gist (geom)

Let’s first create a PostgreSQL function that will send the notification:

CREATE FUNCTION public.notify_qgis() RETURNS trigger

    LANGUAGE plpgsql

    AS $$ 

        BEGIN NOTIFY qgis;

        RETURN NULL;

        END; 

    $$;

To create notify on the table we simply add a couple of triggers to call the function on specific events. Here we are sending a signal on DELETE, INSERT and UPDATE events:

CREATE TRIGGER notify_qgis_edit 

  AFTER INSERT OR UPDATE OR DELETE OR TRANSACT ON public.line 

    FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_qgis();

Now if we view our table definition it will look like this:

emp-shared=# \d line

Table "public.line"

   Column   |           Type            |                     Modifiers                     

------------+---------------------------+---------------------------------------------------

 id         | integer                   | not null default nextval('line_id_seq'::regclass)

 name       | character varying(255)    | not null

 voltage_kv | real                      | not null

 source_id  | integer                   | not null

 geom       | geometry(LineString,4326) | 

Indexes:

  "line_pkey" PRIMARY KEY, btree (id)

  "sidx_line_geom" gist (geom)

Triggers:

notify_qgis_delete AFTER DELETE ON line FOR EACH STATEMENT EXECUTE PROCEDURE notify_qgis()

notify_qgis_edit AFTER INSERT OR UPDATE ON line FOR EACH STATEMENT EXECUTE PROCEDURE notify_qgis()

The last thing you need to do is enable notifications in your layer rendering properties by ticking the ‘refresh layer on notification’ option:

Now you can test by leaving your QGIS Window open and adding features from another machine - you will see they get displayed automatically on yours!

For more info and a nice video demo, see Oslandia’s post on NOTIFY including how to trigger actions from NOTIFY: https://oslandia.com/en/2017/10/07/refresh-your-maps-from-postgresql/