Writing SQL Query to create spatial view?

Question

I use this sql query to draw a rectangle from a point layer (overview):

create or replace view view_test as
select name, width, height, ST_SetSRID(ST_GeomFromText('POLYGON (('
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||'))'),32632) as geom
from overview;

But QGIS isn't loading the results:

2017-07-14T21:38:12 1   dbname='XXXX' host=XX.XX.XXX.XXX port=5432 user='postgres' sslmode=disable key='width' table="public"."view_test" (geom) sql= is an invalid layer - not loaded

Whats wrong with my code?

Answer 1

From the QGIS wiki:

When you don't have a unique id for each row you want to display, you can create on on the fly using the SQL windows to load your geometries. Simply add in the select : SELECT row_number() OVER( ORDER BY field1,field2 ) AS id_qgis , and select id_qgis as the id column in the QGIS list. It is necessary to specify the Order By clause to ensure the same ID is always assigned to the same row, for every query, regardless of the usage of fields and joins that do affect the row order in the result set.