PostGIS syntax (function()).*

Question

Whilst reading the documentation about using the PostGIS function ST_PixelAsCentroids() I was confused somewhat by this (ST_PixelAsPoints(rast, 1)).* syntax and as a newbie to SQL I was wondering if someone could explain this convention and why it's used?

It's obviously used as a method to unbundle complex results but I don't even know what it is called to google it properly to get a formal understanding of it.

I realise this post may be better suited to a SQL forum on Stack but I'll see what GIS Stack conjures up.

Answer 1

The function returns a table with typed columns (a schema) and rows (values). The .* at the end means "all columns from the table." SELECT * FROM f(x) would give a similar output to SELECT (f(x)).*.

Due to a PostgreSQL quirk functions with multiple columns may be called once for each column by the query planner in the cases above. So it is advised to use a Common Table Expression (CTE) or subquery to prevent this behaviour, writing for example:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

or in PostgreSQL 9.3

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;

Answer 2

This is more of a PostgreSQL question rather than a PostGIS question, but sill a good one.

Functions like ST_PixelAsPoints, ST_PixelAsPolygons, and ST_PixelAsCentroids return a set of composite record data type, which have several fields. This is very similar to ST_Dump, described here. To gain access to all the returned fields from these composite data types, they need to be in the form (function(x)).*, as described in the PostgreSQL manual.

Note that function(x).* will not work, and is a common gotcha.