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
.