Re: Using Lateral

Thanks David and Paul,

You have helped me a lot.

Regards
Johann.

On 28 March 2018 at 20:49, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tuesday, March 27, 2018, Johann Spies <johann(dot)spies(at)gmail(dot)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.

--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)