Обсуждение: powerset?

Does anybody have a stored proc they'd like to share (preferably pl/
pgsql) that generates the power set of an array? For instance:

select powerset({1,2,3});

....would give 8 rows:

{}
{1}
{2}
{3}
{1,2}
{2,3}
{1,3}
{1,2,3}

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

On Sat, Sep 23, 2006 at 11:47:59PM -0600, Michael Fuhr wrote:
>     FOR i IN 0 .. (1 << (aupper - alower + 1)) - 1 LOOP

To handle empty arrays this should be:

FOR i IN 0 .. COALESCE((1 << (aupper - alower + 1)) - 1, 0) LOOP

--
Michael Fuhr

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