I just wanted to see if it was possible, it is not a sane thing to do.

PostgreSQL has a notification event system where you NOTIFY on a notification channel and clients can LISTEN for events.

QGIS already supports this for a convenient, remotely triggered refresh function of PostGIS layers by listening on the qgis channel if a PostgreSQL connection exists:

Load a PostGIS layer
Set its symbology to something dynamically randomized so you see changes if the map is refreshed
In the layer’s rendering options set it to refresh on notification
In a PostgreSQL client trigger a notification event, e.g. NOTIFY qgis, 'hi'; or SELECT pg_notify('qgis', 'refresh plz!');
The layer is refreshed in QGIS
You can set the layer(s) to refresh on any event on the qgis channel or only for specific messages. For example you could set each PostGIS layer to refresh on a different message, e.g. their table name and use that in a PostgreSQL trigger.

This is currently bound to existing PostGIS layers and as far as I know, you cannot do more than refresh layers. Edit: You can also trigger layer actions (thanks to the great people at Oslandia)!

But…

QGIS comes with PyQt and PyQt (of course) has its own API to let you talk to database servers. And luckily its QSqlDriver class even has a notification signal that let’s you react to notification events in the most convenient way.

So I wrote something:

from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery, QSqlDriver


def exec_notification(name, source, payload):
    """ZOMG don't ever do this!"""
    try:
        exec(payload.replace('\\n', '\n'))
    except SyntaxError as e:
        iface.messageBar().pushMessage("PG NOTIFY", str(e))

db = QSqlDatabase.addDatabase("QPSQL")
db.setConnectOptions("service=foo")  # using ~/.pg_service.conf
if not db.open():
    raise Exception(db.lastError().databaseText())
db.driver().subscribeToNotification("qgis_qtsql_channel")

db.driver().notification[
    str, QSqlDriver.NotificationSource, "QVariant"
].connect(exec_notification)

Run it in QGIS’ script editor and now you can remotely execute any Python code by sending it from PostgreSQL. For example:

SELECT pg_notify(
	'qgis_qtsql_channel',
	array_to_string(
		array[
			'project = QgsProject.instance()',
			'project.read("map.qgz")'
		],
		'\n'
	)
)


SELECT pg_notify(
	'qgis_qtsql_channel',
	'iface.mapCanvas().redrawAllLayers()'
);


SELECT pg_notify(
	'qgis_qtsql_channel',
	'iface.actionExit().trigger()'
);

Of course this is a horrible idea and you should never run code that a third-party sends to you. Instead you should write specific methods on the Python side and sent well defined messages to control exactly which methods get executed. But, hey it works!

Ideas

Trigger a notification if a PostgreSQL-stored QGIS project gets updated and in QGIS notify the user if they have said project open.
Make a tiny plugin that lets the user specify a mapping of message -> Python code scripts
TBC

Addendum

At first I had used a simple db.driver().notification.connect(on_notification), expecting the slot to simply receive all the arguments that the notification signal would send. But this led to Qt using a different notification signal (just sending the channel name) or something because the name is overloaded. Qt super explainer eyllanesc once again saved the day by showing that one can (and in this case needs to) specify the correct signature so Qt picks the correct signal to connect to. This was a major learning for me.

For debugging I used another slot function connected to the signal which uses both the QGIS message log and the message bar to inform the user about incoming notification events, take it if you want:

def on_notification(name, source, payload):
    message = f"{name} from {source}: {payload}"
    QgsMessageLog.logMessage(message, "PG NOTIFY")
    iface.messageBar().pushMessage("PG NOTIFY", message)

A bad surprise to me was that my QGIS installation on Ubuntu 22.04 (using https://qgis.org/ubuntu) did not have a dependency graph that included PostgreSQL support in QtSql. Ouch! I would have thought this would be usable in any standard QGIS installation out of the box. I had to install libqt5sql5-psql. On Windows this seems to be no issue, both a MSI installer and an advanced OSGeo4W install had the necessary modules. Phew!

Hannes ihm sein Blog

tagline goes here

Category Archives: postgresql

Remotely controlling QGIS through PostgreSQL queries 🤪

Ideas

Addendum

Calculating the metric distance between latitudes with PostGIS