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.

score 3 · Accepted Answer · edited May 23 '17 at 12:39

3

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;

edited May 23 '17 at 12:39

Community

1

answered Oct 22 '14 at 23:36

Jason Scheirer

18,002
2
53
72

2

The two forms aren't quite equivalent; SELECT * FROM function() will execute the function once, SELECT (function()).* will execute the function once per column in the output. – dbaston Oct 22 '14 at 23:45
I edited with a link to a good stackoverflow Q & A explaining the issue @dbaston raised – raphael Oct 23 '14 at 21:50

score 3 · Answer 2 · edited Apr 13 '17 at 12:34

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.

PostGIS syntax (function()).*

2 Answers2