How to add temporary PostGIS table to canvas?

Question

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.

Answer 1

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)

Answer 2

Ok, I figured it out. All I needed is to delete CREATE part from query. There is actually no need to create temporary table because we do not use interface that is used to add existing tables and views. Instead we use interface that executes SELECT clause. We just need to pass a clause that selects data that is needed (so it is already virtually created and there is no need to create tables explicitly).

uri = QgsDataSourceURI()
uri.setConnection(server, port, db_name, login, password)
sql = "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)