I need to add a subquery to a working function. Instead of select * from table, I'd like to select * from (select * from table where height>1500) as sub, something that works in other functions I have.
However, it doesn't work in this function. See below:
This works as expected:
select row_to_json(t) from ( select * from table where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299 85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042 5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9 59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942 548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628 786],[42.56236553192139,37.96026293121387]]]}' ), 4326), 3857), table.geom)) as t;This doesn't work (added subquery):
select row_to_json(t) from ( select * from ( select * from table where height>1500 ) sub where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299 85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042 5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9 59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942 548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628 786],[42.56236553192139,37.96026293121387]]]}' ), 4326), 3857), table.geom)) as t;How can I do this? I need to add the string (SELECT * FROM table WHERE height>1500) AS sub every time, as this is set somewhere else and needs to be included wholly.
أكثر...
However, it doesn't work in this function. See below:
This works as expected:
select row_to_json(t) from ( select * from table where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299 85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042 5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9 59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942 548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628 786],[42.56236553192139,37.96026293121387]]]}' ), 4326), 3857), table.geom)) as t;This doesn't work (added subquery):
select row_to_json(t) from ( select * from ( select * from table where height>1500 ) sub where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299 85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042 5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9 59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942 548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628 786],[42.56236553192139,37.96026293121387]]]}' ), 4326), 3857), table.geom)) as t;How can I do this? I need to add the string (SELECT * FROM table WHERE height>1500) AS sub every time, as this is set somewhere else and needs to be included wholly.
أكثر...