On Thursday, September 29, 2011, bricklen <brick...@gmail.com> wrote: > I recently had need of an "array_except" function but couldn't find > any good/existing examples. Based off the neat "array_intersect" > function at http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Intersection_of_arrays , > I put together an "array_except" version to return the array elements > that are not found in both arrays. > Can anyone think of a faster version of this function? Maybe in C? > The generate_series example takes about 3.5s on the dev db I'm testing > on, which isn't too bad (for my needs at least). > > create or replace function array_except(anyarray,anyarray) returns > anyarray as $$ > select array_agg(elements) > from ( > (select unnest($1) except select unnest($2)) > union > (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,run}'::text[]); > > select array_to_relation(arr) > from array_except( (select array_agg(n) fro> generate_series(1,1000000,1) as n), > (select array_agg(n) from > generate_series(5,1000005,1) as n) > ) as arr; > > I'm testing on 9.0.4 >r > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
*) 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)
