Re: Using Lateral

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(at)illuminatedcomputing(dot)com