postgresql - Insert forms from a query in postgis -

i try insert polygon in table geofield query. table geofield defined this:

create table geofield(id serial primary key, field geometry);  insert geofield(field) values (myfield) select     'polygon(('||cast((select lat coordinates id=1)as varchar)||' '||       cast((select lon coordinates id=1)as varchar)||','||       cast((select lat coordinates id=2)as varchar)||' '||       cast((select lon coordinates id=2)as varchar)||','||       cast((select lat coordinates id=3)as varchar)||' '||       cast((select lon coordinates id=3)as varchar)||','||       cast((select lat coordinates id=4)as varchar)||' '||       cast((select lon coordinates id=4)as varchar)||','||       cast((select lat coordinates id=1)as varchar)||' '||       cast((select lon coordinates id=1)as varchar)||'))') myfield; 

the select alone returns me:

polygon((46.744628268759314 6.569952920654968,46.74441692818192 6.570487107359068,46.74426116111054 6.570355867853787,46.74447250168793 6.569821681149689,46.744628268759314 6.569952920654968)) 

but doesn't work, tryed st_geomfromtext same result.

i avoid working text concatenation , work plain postgis functions instead:

select     /* make polygon linestring */     st_makepolygon(         /* close polygon */         st_addpoint(             t.linestring,             st_startpoint(t.linestring)         )     ) (     select         /* aggregate linestring */         st_makeline(st_makepoint(c.lon, c.lat) order c.id) linestring     coordinates c ) t ; 

following prior question, json directly if wish:

select     /* make polygon linestring */     st_makepolygon(         /* close polygon */         st_addpoint(             t.linestring,             st_startpoint(t.linestring)         )     ) (     select         /* aggregate linestring */         st_makeline(st_makepoint(c.lon, c.lat) order c.id) linestring         (         select             e.id,             (e.element->>'lat')::numeric lat,             (e.element->>'lon')::numeric lon                     field f,             json_array_elements(f.data->'vertices') ordinality e(element, id)     ) c ) t ;