[GIS] How to add temporary PostGIS table to canvas

postgispyqgispythonqgis

I'm working with PostGIS database. I would like to create temporary table with geometry and add it as a layer to QGIS. There is no problem with regular tables, but I can't figure out how to add temporary tables. I checked source code of DB Manager plugin and found this key line: uri.setDataSource("", u"(%s\n)" % sql, geomCol, "", uniqueCol). With this in mind I created my code:

uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM some_spatial_table"
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'gid') # 'geom' and 'gid' are present in the table
vlayer = QgsVectorLayer(uri.uri(), 'layer_name', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

The SQL-query is Ok – I'm able to execute it in DB Manager and add resulting table to map. But my code doesn't produce a layer in QGIS and there is no exception raised. What is wrong?

UPD:
There is an output in PostGIS tab in QGIS logs:

Unable to execute the query.
            The error message from the database was:
            ERROR:  syntax error at or near "CREATE"
            LINE 2:                         CREATE or replace TEMP VIEW sp_temp_...
                                            ^
            .
            SQL: SELECT * FROM (
                                    CREATE or replace TEMP VIEW sp_temp_2015_10_25_02_29 AS
                                    WITH posts AS (
                                    SELECT post_name, date, pollutant, measurement, type_measure
                                    FROM air_observations
                                    WHERE pollutant = '301' AND date = '2014-05-05'
                                    )
                                    SELECT air_posts.gid, air_posts.geom, air_posts.name,
                                    posts.date, posts.pollutant, posts.measurement, posts.type_measure
                                    FROM air_posts
                                    INNER JOIN posts
                                    ON air_posts.name = posts.post_name;
            ) AS "subQuery_0" LIMIT 1

Starting from "CREATE" and ending with ";" is an actual query that is passed as sql.

Best Answer

The code you are using is for adding and loading a datasource to the registry. But the SQL to use inside uri.setDataSource must return records. That's not the case of your query, that starts with a CREATE.

I believe that you need to create the temporary table in a different steep, and then add it to the registry.

Try this:

# Create the temporary table
db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(server)
db.setPort(port)
# non spatial table or view
db.setDatabaseName(db_name)
db.setUserName(login)
db.setPassword(password)
query = QSqlQuery(db)
query.exec_("CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM some_spatial_table")

# Add the Layer
uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "SELECT * FROM temp_1"
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'gid') # 'geom' and 'gid' are present in the table
vlayer = QgsVectorLayer(uri.uri(), 'layer_name', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
Related Question