Re: Subtract one array from another, both with non-unique elements

2016-03-08 15:59 GMT+02:00 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:

> Here 1-pass version, if you have improvement suggestions, you are welcome -
>

My variant:

CREATE OR REPLACE FUNCTION arrexcept(anyarray, anyarray) RETURNS anyarray
AS $arrexcept$
SELECT array_agg(un) FROM (
SELECT un, row_number() OVER (PARTITION BY un ORDER BY ord) id FROM
unnest($1) with ordinality AS t(un, ord)
EXCEPT
SELECT un, row_number() OVER (PARTITION BY un ORDER BY ord) id FROM
unnest($2) with ordinality AS t(un, ord)
) x;
$arrexcept$ LANGUAGE sql;

postgres=# select arrexcept(ARRAY['A','A','B','B','C'], ARRAY['A','B']);
arrexcept
-----------
{A,B,C}
(1 row)

But it doesn't preserves the order of the elements, not sure if this is
important.

--
Victor Y. Yegorov