Re: array_except -- Find elements that are not common to both arrays

On Tue, Oct 4, 2011 at 2:16 AM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> On 01/10/11 01:23, Vitalii Tymchyshyn wrote:
>>
>> Since you are using except and not except all, you are not looking at
>> arrays with duplicates.
>> For this case next function what the fastest for me:
>>
>> create or replace function array_except2(anyarray,anyarray) returns
>> anyarray as $$
>> select ARRAY(
>> (
>> select r.elements
>> from    (
>>        (select 1,unnest($1))
>>        union all
>>        (select 2,unnest($2))
>>        ) as r (arr, elements)
>>    group by 1
>>    having min(arr)=max(arr)
>> ))
>> $$ language sql strict immutable;
>>
>> Best regards, Vitalii Tymchyshyn
>>
> Very neat!
>
> I could see that this function could trivially be modified to handle 3
> arrays.
>
> QUESTION: Could this be modified to take an arbitrary number of arrays?

hm good question. not in sql aiui, because variadic arguments are
pushed through as arrays, and there is no such thing in postgres as a
'anyarray[]' (or any array of array for that matter).

in c, you get to do more detail processing of variadic arguments, so
you could probably rig something that way -- but the implementation
would be completely different.

alternate way to avoid the variadic problem would be to make an xor()
aggregate which chains the arrays down using the 'all sql' method
posted above -- not as fast maybe, but pretty darn cool if you ask me.

merlin