how to convert CTE to nested SELECT-CodePudding

i have the below posted query. i would like to use nested SELECT statement instead of CTE. i would like to have the outer SELECT statement stating the columns that are contained in the table grid_cell_data i do not want to use j.* i want to use the columns names instead

query:

WITH j AS (
 SELECT
   ST_AsMVTGeom(
     st_geomfromtext(geometryofcellrepresentativetobuffer),
     ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096,4096)))
 FROM grid_cell_data where id = 3
)
SELECT ST_AsMVT(j.*) FROM j

grid_cell_data table

CREATE TABLE IF NOT EXISTS grid_cell_data (
   id SERIAL PRIMARY KEY,
   isTreatment boolean,
   isBuffer boolean,
   geometryOfCellRepresentativeToTreatment geometry,
   geometryOfCellRepresentativeToBuffer geometry 
)

CodePudding user response：

You just need to select all records you need in the CTE/Subquery and in the outer query you can keep using j or j.*. In case you need to further select specific columns, place them between parenthesis in the SELECT, so that it represents a record for ST_AsMVT, e.g.

WITH j AS (
 SELECT *,
   ST_AsMVTGeom(
     st_geomfromtext(geometryofcellrepresentativetobuffer),
     ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096,4096))) AS mvt_geom
 FROM grid_cell_data where id = 3
)
SELECT ST_AsMVT((id,isTreatment,isBuffer,mvt_geom)) 
FROM j