Very nice, thanks!
On Sep 23, 2006, at 10:47 PM, Michael Fuhr wrote:
On Fri, Sep 22, 2006 at 11:38:12PM -0700, Ben wrote:
Does anybody have a stored proc they'd like to share (preferably pl/
pgsql) that generates the power set of an array?
Here's an attempt:
CREATE FUNCTION powerset(a anyarray) RETURNS SETOF anyarray AS $$
DECLARE
retval a%TYPE;
alower integer := array_lower(a, 1);
aupper integer := array_upper(a, 1);
j integer;
k integer;
BEGIN
FOR i IN 0 .. (1 << (aupper - alower + 1)) - 1 LOOP
retval := '{}';
j := alower;
k := i;
WHILE k > 0 LOOP
IF k & 1 = 1 THEN
retval := array_append(retval, a[j]);
END IF;
j := j + 1;
k := k >> 1;
END LOOP;
RETURN NEXT retval;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Since this is a set-returning function you'd call it as follows:
SELECT * FROM powerset(ARRAY[1,2,3]);
powerset
----------
{}
{1}
{2}
{1,2}
{3}
{1,3}
{2,3}
{1,2,3}
(8 rows)
If you wrap the PL/pgSQL function with an SQL function then you
could call it from the SELECT list:
CREATE FUNCTION powerset2(anyarray) RETURNS SETOF anyarray AS $$
SELECT * FROM powerset($1);
$$ LANGUAGE sql IMMUTABLE STRICT;
CREATE TABLE foo (id serial PRIMARY KEY, a integer[]);
INSERT INTO foo (a) VALUES ('{1,2}');
INSERT INTO foo (a) VALUES ('{10,20,30}');
SELECT id, powerset2(a) FROM foo;
id | powerset2
----+------------
1 | {}
1 | {1}
1 | {2}
1 | {1,2}
2 | {}
2 | {10}
2 | {20}
2 | {10,20}
2 | {30}
2 | {10,30}
2 | {20,30}
2 | {10,20,30}
(12 rows)
Will that work for you?
--
Michael Fuhr
