Re: Using Lateral

On 03/28/2018 05:22 AM, Johann Spies wrote:
> Thanks Paul.
>
> I was hesitant to post my full query. It is a long and complicated
> query.

Ha ha, you aren't joking. :-)

With something that long I don't think I'd want to split every xpath
call into a new join. I guess you could try it and see though.

Stepping back, your original query is prepared for xpath to give 0
results or 1 result, but not 2+, and the problem is that you're getting
a 2+ result from the address_spec element. What is the right behavior
there? To get two rows in the end result? Just use the first/last
address_spec?

If you want to force 0/1 results, you can keep your structure and do this:

SELECT DISTINCT ut,
(xpath('//t:address_spec/t:country/text()', q.address_spec,
p.ns))[1]::citext AS country,
(xpath('//t:address_spec/t:city/text()', q.address_spec,
p.ns))[1]::citext AS city,
(xpath('//t:organizations/t:organization/text()',
q.address_spec, p.ns))[1]::citext AS organisation,
(xpath('//t:organizations/t:organization[(at)pref="Y"]/text()',
q.address_spec, p.ns))[1]::citext AS prefname,
(xpath ('//t:suborganizations/t:suborganization/text()',
q.address_spec, p.ns))[1]::citext AS suborgname,
(xpath ('/t:address_spec/@addr_no', q.address_spec,
p.ns))[1]::text::integer AS addr_no

(Actually I see you are already doing that for addr_no. And an aside:
did you intend `/t:address_spec` instead of `//t:address_spec` there?)

If you would prefer to get multiple rows back, then you'll probably need
a subquery to give one row per `xpath('//t:address_spec')`, so that you
can keep the cities/countries/addr_nos together.

Anyway, I hope that's enough to get you started on the right path! The
root cause is that UNNEST is giving you two rows where it only makes
sense to have one, so you need to restrict that or pull it into a
context where several rows are allowed (like a join).

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com