Re: Setting all elements in an Bool[] array to the same value - Mailing list pgsql-general

On Thu, Jun 09, 2005 at 06:10:28PM -0700, Otto Blomqvist wrote:
>
> Is there any way to set all elements in a  long boolean array (bool[]) to
> the same value ?

In PostgreSQL 7.4 and later you could write a polymorphic function
to fill any type of array.  Here's a simple example that handles
one-dimensional arrays:

CREATE FUNCTION array_fill(anyarray, anyelement) RETURNS anyarray AS '
DECLARE
    a  $0%TYPE := ''{}'';
    i  integer;
BEGIN
    FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP
        a[i] := $2;
    END LOOP;

    RETURN a;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE TABLE foo (
    id      serial PRIMARY KEY,
    barray  boolean[],
    iarray  integer[]
);

INSERT INTO foo (barray, iarray) VALUES ('{t,f}', '{1,2,3}');
INSERT INTO foo (barray, iarray) VALUES ('{t,f,t,f}', '{4,5,6,7,8,9}');

SELECT * FROM foo ORDER BY id;
 id |  barray   |    iarray
----+-----------+---------------
  1 | {t,f}     | {1,2,3}
  2 | {t,f,t,f} | {4,5,6,7,8,9}
(2 rows)

UPDATE foo SET barray = array_fill(barray, false),
               iarray = array_fill(iarray, 0);

SELECT * FROM foo ORDER BY id;
 id |  barray   |    iarray
----+-----------+---------------
  1 | {f,f}     | {0,0,0}
  2 | {f,f,f,f} | {0,0,0,0,0,0}
(2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/