Cannot load postgis query as layer in QGIS using create table command

Question

I've a multilinestring object and a points object , where the points are located on the line. The user can set a start and end point on the line and my goal is to return a subset of the points that are within start_point and end_point on the line.

I first create a temp table (subline_table) using ST_LineSubString to get the linestring within start_point and end_point and then query my points object with ST_DWithin using this subline_table. If I run this query in QGIS DB Manager the correct table/points are shown, but this layer will not show up as query layer once loaded.

If I store the point selection as a table, than the output is saved as a table and can be added succesfully as a postgis layer in QGIS.

My query is as follow:

-- SELECT SUBLINE WHERE INPUT POINTS ARE SNAPPED TO LINE AND STORE IN TEMP TABLE
CREATE TEMP TABLE subline_table (geom) ON COMMIT DROP AS
SELECT ST_LineSubString(line.sub, 
ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_start WHERE Id = 0)),
ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_end WHERE Id = 1))
)
FROM (SELECT ST_LineMerge((SELECT geom FROM linestring WHERE id = 2)) AS sub) AS line; 

-- SELECT POINTS WITHIN DISTANCE OF SUBLINE
-- CREATE TABLE subpoints_table AS
SELECT
  points.geom,
  points.gid,
  points.id
FROM tbl_all_points points, subline_table line
WHERE ST_DWithin(points.geom, line.geom, 20000)

Questions:

1. Is it possible to add a querylayer to QGIS if there is a CREATE TABLE command in the query?

2. If no, any suggestions how to do this query without the need to write the subline to a table?

Answer 1

I just placed the first bit that i wrote to a seperate table on the place where i refer to the table. Where eventually subline.st_linesubstring was the tricky bit.

-- SELECT SUBLINE WHERE INPUT POINTS ARE SNAPPED TO LINE
-- SELECT SUBPOINTS WITHIN DISTANCE OF SUBLINE
SELECT
  points.geom,
  points.id
FROM 
  tbl_all_points AS points, 
  (SELECT ST_LineSubString(line.sub, 
    ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_start WHERE Id = 0)),
    ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_end WHERE Id = 1))
  )
  FROM (SELECT ST_LineMerge((SELECT geom FROM linestring WHERE id = 2)) AS sub) AS line) AS subline
WHERE ST_DWithin(points.geom, subline.st_linesubstring, 20000)