Re: Setting all elements in an Bool[] array to the same value

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/