Thanks David and Paul,
You have helped me a lot.
Regards
Johann.
On 28 March 2018 at 20:49, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Tuesday, March 27, 2018, Johann Spies <johann.spies@gmail.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)
