Обсуждение: Setting all elements in an Bool[] array to the same value

Поиск
Список
Период
Сортировка

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

От
"Otto Blomqvist"
Дата:
Hello !

Is there any way to set all elements in a  long boolean array (bool[]) to
the same value ?

update testbool set "all elements" = false;    or so ? ;)

Any ideas ?

Thanks

/Otto Blomqvist

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

От
Michael Fuhr
Дата:
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/

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

От
Gnanavel Shanmugam
Дата:
try this,
test=# select array(select  1 from
generate_series(0,array_upper('{1,2,3,4,5}'::int[],1)));
   ?column?
---------------
 {1,1,1,1,1,1}
(1 row)

test=# select array(select  true  from
generate_series(0,array_upper('{1,2,3,4,5}'::int[],1)));
   ?column?
---------------
 {t,t,t,t,t,t}
(1 row)

with regards,
S.Gnanavel


> -----Original Message-----
> From: mike@fuhr.org
> Sent: Fri, 10 Jun 2005 04:56:55 -0600
> To: o.blomqvist@secomintl.com
> Subject: Re: [GENERAL] 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq