[SOLVED] How to add PostGIS SQL SELECT query as layer to QGIS 3 project using Python console?

How to add PostGIS SQL SELECT query as layer to QGIS 3 project using Python console?

I want to add a PostGIS SELECT query as a new layer to a QGIS 3 project using the Python console.

It is a simple process to do this using the SQL Window in the DB Manager of QGIS. Here you can create a layer from any SQL query of a PostGIS enabled PostgreSQL database.

The following works for adding an entire PostGIS table and I'm aware a filter can be added by providing a fourth argument to uri.setDataSource(). However, this method, to my knowledge, does not allow you to do multi table queries and joins.

from qgis.core import *
from qgis.core import QgsVectorLayer, QgsDataSourceUri
from qgis.core import QgsProject

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "my_database", "postgres", "password")

uri.setDataSource("public", "my_table", "geom")

vlayer = QgsVectorLayer(uri.uri(), "my_new_layer", "postgres")
QgsProject.instance().addMapLayer(vlayer, True)

Ideally I would like to replace "my_table" with an SQL query like SELECT * FROM my_table. This would allow you to extend functionality to more complicated queries like:

SELECT * FROM my_table_1
JOIN my_table_2 ON my_table_1.foreign_key = my_table_2.primary_key;

The eventual goal is to then implement this code in a QGIS plugin.

Any help would be much appreciated!

Solution

I found the solution here:

https://gis.stackexchange.com/a/197800

Simply, you must leave the schema blank and include it in the SQL query instead.