Algebraic Data Types in Postgres, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create function valid_any_domain(anyelement, variadic regtype[])
  returns boolean
  language plpgsql
  immutable
as $func$
declare
  t regtype;
begin
  foreach t in array $2 loop
    begin
      execute format('select $1::%s', t) using $1;
    exception
      when not_null_violation or check_violation then
        continue;
    end;
    return true;
  end loop;

return false;
end;
$func$;

create domain hoofed as text
  check (value in ('horse', 'goat'));

create domain monkey as text
  check (value in ('chimp','macaque'));

create domain animal as text
  check (valid_any_domain(value, 'hoofed', 'monkey'));

create table animal_tbl(
  animal_col animal
);

insert into animal_tbl(animal_col)
values ('horse'), ('goat'), ('chimp'), ('macaque');

select * from animal_tbl;

alter domain hoofed drop constraint hoofed_check;
alter domain hoofed add check (value in ('horse', 'goat', 'zebra'));
alter domain animal validate constraint animal_check;

insert into animal_tbl(animal_col)
values ('zebra');

select * from animal_tbl;

alter domain hoofed drop constraint hoofed_check;
alter domain hoofed add check (value in ('horse', 'goat'));
alter domain animal validate constraint animal_check;


Absolute service time: 0,47 sec, absolute service time: 0,48 sec