PostgreSQL: SELECT a function on all items in an array [duplicate]

Question

Consider a table with the following rows:

id       | bigint
polygons | geometry(Polygon,4326)[]

SELECT-ing the polygons row returns an array of unreadable binary data:

SELECT polygons FROM some_table WHERE id=405;
{0103000020E61000000100000006000000B84F039E5AC0E375243935C13F402...}

Using st_AsText on the first element returns a readable output:

SELECT st_AsText(polygons[1]) FROM some_table WHERE id=405;
POLYGON((-106.4689521119 31.7547183717742 ...)

Unsurprisingly, the function only works on an element, not on an array:

SELECT st_AsText(polygons) FROM some_table WHERE id=405;
ERROR:  function st_astext(geometry[]) does not exist

Putting it in Python lingo, I'm looking for the equivalent of print [st_AsText(p) for i in polygons] in PostgreSQL.

How do I run a PostgreSQL function on all elements of an array within a SELECT statement, a-la Python's list comprehension?

Addendum I think that this isn't a strict duplicate, because How to apply a function to each element of an array column in Postgres? handles an inline array (e.g. FROM unnest(ARRAY[1.53224,0.23411234])), while this question handles a SELECT statement from a table which contains an array column. The conversion isn't trivial, at least for me.

klin · Accepted Answer · 2016-01-04 13:48:52Z

8

Use unnest():

SELECT st_AsText(p) 
FROM some_table, unnest(polygons) p 
WHERE id=405;

answered Jan 4, 2016 at 13:48

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: SELECT a function on all items in an array [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related