Postgresql check constraint on all individual elements in array using function

user_1145723 0
Viewed 3491

If I have a table

create table foo ( bar text[] not null check ..... );

and a function

create function baz(text) returns boolean as $$ .....

How do I add a check constraint to the foo table such that every element in the bar field validates the baz function?

I'm thinking that I need to create a function

create function array_baz(arg text[]) returns boolean as $$ 
    with x as ( select baz(unnest(arg)) as s_arg )
    select not exists (select 1 from x where s_arg = false)
$$ language sql strict immutable;

 create table foo (bar text[] not null check ( array_baz(bar) = true ) );

However, I'm sure that I'm reinventing the wheel here and there's a cuter way of doing this. What psql trick am I missing? A map function would be nice

create table foo (bar text[] not null check (true = all(map('baz', bar)));

but so far my search efforts are fruitless.

postgresql arrays
2 Answers

Check constraints can be applied to individual items in an array by defining a domain type:

CREATE DOMAIN lower_text AS text
CHECK(
  VALUE = lower(VALUE)
);

CREATE TABLE test (
  name lower_text[] NOT NULL
);
user_18098720
Related