PostGIS returns record as datatype. This is unexpected

Question

I have this query

WITH buffered AS (
    SELECT 
        ST_Buffer(geom , 10, 'endcap=round join=round') AS geom,
        id
    FROM line),
hexagons AS (
    SELECT 
        ST_HexagonGrid(10, buffered.geom) AS hex,
        buffered.id
    FROM buffered
) SELECT * FROM hexagons;

This gives the datatype record in the column hex. This is unexpected. I expect geometry as a datatype. Why is that?

Answer 1

According to the documentation, the function ST_HexagonGrid returns a setof record. These records contain however a geometry attribute called geom, so in order to access the geometry of this record you have to wrap the variable with parenthesis () and call the attribute with a dot ., e.g.

SELECT (hex).geom FROM hexagons;

or just access fetch all attributes using * (in this case, i,j and geom):

SELECT (hex).* FROM hexagons;

Demo (PostGIS 3.1):

WITH j (hex) AS (
 SELECT 
  ST_HexagonGrid(
   10,ST_Buffer('LINESTRING(-105.55 41.11,-115.48 37.16,-109.29 29.38,-98.34 27.13)',1))    
)
SELECT ST_AsText((hex).geom,2) FROM j;

                                       st_astext                                        
----------------------------------------------------------------------------------------
 POLYGON((-130 34.64,-125 25.98,-115 25.98,-110 34.64,-115 43.3,-125 43.3,-130 34.64))
 POLYGON((-115 25.98,-110 17.32,-100 17.32,-95 25.98,-100 34.64,-110 34.64,-115 25.98))
 POLYGON((-115 43.3,-110 34.64,-100 34.64,-95 43.3,-100 51.96,-110 51.96,-115 43.3))
 POLYGON((-100 34.64,-95 25.98,-85 25.98,-80 34.64,-85 43.3,-95 43.3,-100 34.64))

As ST_HexagonGrid returns a setof record, you can access the record atributes using a LATERAL as described here, or just call the function in the FROM clause:

SELECT i,j,ST_AsText(geom,2) FROM 
  ST_HexagonGrid(
   10,ST_Buffer('LINESTRING(-105.55 41.11,-115.48 37.16,-109.29 29.38,-98.34 27.13)',1));

 i  | j |                                       st_astext                                        
----+---+----------------------------------------------------------------------------------------
 -8 | 2 | POLYGON((-130 34.64,-125 25.98,-115 25.98,-110 34.64,-115 43.3,-125 43.3,-130 34.64))
 -7 | 1 | POLYGON((-115 25.98,-110 17.32,-100 17.32,-95 25.98,-100 34.64,-110 34.64,-115 25.98))
 -7 | 2 | POLYGON((-115 43.3,-110 34.64,-100 34.64,-95 43.3,-100 51.96,-110 51.96,-115 43.3))
 -6 | 2 | POLYGON((-100 34.64,-95 25.98,-85 25.98,-80 34.64,-85 43.3,-95 43.3,-100 34.64))

Further reading: How to divide world into cells (grid)