|
On 6/24/19 5:19 PM, David G. Johnston
wrote:
On
6/24/19 4:46 PM, Alex Magnum wrote:
Yes, they are.
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?
|
