On 03/27/2018 03:22 AM, Johann Spies 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,
No longer. The error message suggests I should use a lateral query.
But I could not figure out in the documentation how to get the same
result using a "lateral" construct.
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.
It is hard to suggest something without seeing your whole query (e.g.
how are you joining q & p?). But it sounds like you basically want a
left join to the unnested xpath result. It could be a lateral join or not.
It is common to use UNNEST with an implicit lateral join, like this:
SELECT ...
FROM q, UNNEST(xpath('...', q.address_spec))
But that gives you an inner join. To get an outer join you need to be
more explicit. Something like this:
SELECT ...
FROM q, p
LEFT OUTER JOIN LATERAL (
SELECT *
FROM unnest(xpath('//t:address_spec/t:country/text()',
q.address_spec, p.ns))::citext
) x(country)
ON true
(Presumably you would do something more restrictive to connect q & p
though.)
Yours,
--
Paul ~{:-)
pj@xxxxxxxxxxxxxxxxxxxxxxxx
