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@illuminatedcomputing.com