Can PostgreSQL select from an array returned by a function?

Question

This query fails:

SELECT xpath('/my/xpath/expr', my_xml)[1] FROM my_table

ERROR:  syntax error at or near "["

But this one works:

SELECT x[1] FROM
    (SELECT xpath('/my/xpath/expr', my_xml) as x FROM my_table) as ss

My xpath expression always returns only a single value, but the Postgres xpath function returns an array. I want to select the first value in the array. While the subselect works, it's pretty ugly.

Why doesn't the first query work, and is there a cleaner way to do this than the second query?

Answer 1

How about this:

SELECT (xpath('/my/xpath/expr', my_xml))[1] FROM my_table;