Using Lateral

On Tuesday, March 27, 2018, Johann Spies <johann(dot)spies(at)gmail(dot)com> wrote:

> In the past I could use this in a query:
>
> SELECT
> DISTINCT ut,
> CASE
> WHEN xpath_exists ('//t:address_spec/t:country/text()',
> q.address_spec,
> p.ns)
> THEN unnest (xpath ('//t:address_spec/t:country/text()',
> q.address_spec,
> p.ns))::citext
> ELSE NULL
> END country,
> [...]
> Just selecting "unnest(...)" gives the wrong result because if the
> xpath does not exist all the other parts of the query (like 'ut' in
> this case) also are empty.
>

You should be able to solve the empty-set-in-target-list problem via a
scalar subquery instead of a case construct.

Select distinct ut, (select unnest(...)) as country from ...

The subselect wil return null if fed zero rows. Though you will still have
to solve an issue if the unrest returns 1+ rows.

In lieu of the inline scalar subquery I would advise writing a function and
just calling it directly in the target-list. But it should not return
setof, I'd return an array if you need to accept the possibility of 1+
matches, and return an empty array for zero matches.

David J.