Re: Coalesce 2 Arrays

On 6/24/19 5:19 PM, David G. Johnston wrote:
> On Mon, Jun 24, 2019 at 4:11 PM Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>> wrote:
>
>
> On 6/24/19 4:46 PM, Alex Magnum wrote:
>> Yes, they are.
>>
>> On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent
>> <robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>> wrote:
>>
>>
>>
>>> On Jun 24, 2019, at 2:31 PM, Alex Magnum
>>> <magnum11200(at)gmail(dot)com <mailto:magnum11200(at)gmail(dot)com>> wrote:
>>>
>>> Hi,
>>> I have two arrays which I need to combine based on the
>>> individual values;
>>> i could do a coalesce for each field but was wondering if
>>> there is an easier way
>>>
>>> array_a{a,  null,c,   d,null,f,null}  primary
>>> array_b{null,2  ,null,4,5   ,6,null}  secondary
>>>
>>> result {a,   2,   c,   d,5,   f,null)
>>>
>>> Any advice would be appreciated
>>>
>> Are the inputs always of fixed dimensions eg. 1 by 7?
>>
> create or replace function tt( a1 int[], a2 int[])
> returns int[] as $$
> declare
>         aret int[];
>         asize int;
> begin
>      select array_length(a1,1) into asize;
>      for i in 1..asize loop
>           aret[i] = coalesce(a1[i], a2[i]);
>      end loop;
>      return aret;
> end;
>
> $$ language plpgsql;
>
> select * from tt(array[3,null], array[null,4]);
>   tt
> -------
>  {3,4}
> (1 row)
>
> Plain SQL variant:
> SELECT array_agg(COALESCE(a, b))
> FROM (
> SELECT
> unnest(ARRAY[null, 2]::int[]),
> unnest(ARRAY[1,null]::int[])
> ) vals (a, b);
>
> Even if they aren't the same length the above should work, I think, as
> extra rows for the shorter array will contribute padded nulls.
>
> David J.
>
Brilliant of course.  Maybe not as easy to stick in another query

select a.name, b.name, tt(a.intarray, b.intarray) as coalesced_array
from table a join table b on a.<something> = b.<something>;

Any guess at the performance differences?