Correct use of a LATERAL FROM

Question

I am trying to use PostGIS to undertake a number of steps within an SQL statement to basically convert a raster to points, buffer, dissolve and then determine the overall boundary i.e. concave hull based on an attribute.

The following is where I am at but get an error suggesting the use of a LATERAL FROM but I do not know what I need to do.

Any assistance would be greatly appreciated.

[0A000] ERROR: aggregate function calls cannot contain set-returning function calls Hint: You might be able to move the set-returning function into a LATERAL FROM item. Position: 123

 SELECT name,
    st_union(
            st_buffer(
                    st_transform(
                            (ST_PixelAsCentroids(rast, 1)).geom
                        , 32756),
                    50, 2)
        )
 FROM rasters
 where id < 5

Answer 1

Just move the function ST_PixelAsCentroids() to a CROSS JOIN LATERAL and give it a label, e.g.

SELECT name, ST_ASTEXT(
  ST_Union(
    ST_Buffer(
      ST_Transform((j).geom,32756),
      50, 2))
    )
FROM rasters 
CROSS JOIN LATERAL ST_PixelAsCentroids(rast, 1) j
WHERE id < 5
GROUP BY name;

Although you can achieve the same results without the CROSS JOIN LATERAL

SELECT name, ST_ASTEXT(
  ST_Union(
    ST_Buffer(
      ST_Transform((j).geom,32756),
      50, 2))
    )
FROM rasters, ST_PixelAsCentroids(rast, 1) j
WHERE id < 5
GROUP BY name;