Search Postgresql Archives

Re: Using Lateral

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
> On Tuesday, March 27, 2018, Johann Spies <johann.spies@xxxxxxxxx> 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)




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux