On Tuesday, March 27, 2018, Johann Spies <
johann.spies@gmail.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.
