Re: array_except -- Find elements that are not common to both arrays - Mailing list pgsql-performance

On Thu, Sep 29, 2011 at 8:08 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> *) Prefer union all to union
> *) prefer array constructor to array_agg when not grouping.
> *) perhaps consider not reusing 'except' name with different semantic
> meaning
>
> Well done
> merlin (on phone & in bed)

Hi Merlin,

Thanks for the tips. I have implemented suggestion 1 & 2, and that has
shaved about 1/2 of a second off of the generate_series example below
(3.52s -> 3.48s)

Do you have a suggestion for a better name? I considered array_unique,
array_distinct etc, but those don't really describe what is being
returned IMO. Something that conjures up the "return elements that are
not common to both arrays" would be nice.

create or replace function array_except(anyarray,anyarray) returns
anyarray as $$
select ARRAY(
(
select r.*
from    (
        (select unnest($1) except select unnest($2))
        union all
        (select unnest($2) except select unnest($1))
        ) as r (elements)
))
$$ language sql strict immutable;


select array_except('{this,is,a,test}'::text[],'{also,part,of,a,test}'::text[]);

select array_to_relation(arr)
from array_except( (select array_agg(n) from
generate_series(1,1000000,1) as n) , (select array_agg(n) from
generate_series(5,1000005,1) as n) ) as arr;


More improvement suggestions gladly accepted!