More concisely, you can compare directly against all values of the array:
# create table i (i int[] check (0 <= ALL(i) AND 1023 >= ALL(i)));
# insert into i values (ARRAY[0,1,2,3,1023]);
# insert into i values (ARRAY[0,1,2,3,-1]);
ERROR: new row for relation "i" violates check constraint "i_i_check"
# insert into i values (ARRAY[0,1,2,3,1024]);
ERROR: new row for relation "i" violates check constraint "i_i_check"
Joel
On Sat, Sep 8, 2012 at 8:31 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
test=# create or replace function check_array(int[]) returns bool as $declare i int; begin select into i max(unnest) from unnest($1); if i > 10 then return false; end if; return true; end$ language plpgsql ;
CREATE FUNCTION
Time: 0,579 ms
test=*# create table a (i int[] check (check_array(i)));
CREATE TABLE
Time: 6,768 ms
test=*# insert into a values (array[1,2,3]);
INSERT 0 1
Time: 0,605 ms
test=*# insert into a values (array[1,2,30]);
ERROR: new row for relation "a" violates check constraint "a_i_check"