From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Array intersection |
Date: | 2007-10-17 17:04:21 |
Message-ID: | 20071017170421.GC32690@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Oct 17, 2007 at 10:19:43AM -0500, Josh Trutwin wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.
You can use an SQL function, which has the advantage of always being
available :)
It's up to you to ensure that the input arrays have the same type.
Cheers,
David.
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS "the_intersection"
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
INTERSECT
SELECT $2[j] AS "the_intersection"
FROM generate_series(
array_lower($2,1),
array_upper($2,1)
) AS j
);
$$;
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Trutwin | 2007-10-17 18:00:48 | Re: Array intersection |
Previous Message | Sam Mason | 2007-10-17 16:49:01 | Re: Array intersection |