CREATE TABLE aaa(i int);
insert into aaa values ( 1 );
insert into aaa values ( 2 );
insert into aaa values ( 3 );

CREATE OR REPLACE FUNCTION check_aaa(int) RETURNS boolean AS $_$
  select exists(select * from aaa where i=$ 1 ); --сколь угодно сложный запрос
$_$ LANGUAGE sql;

CREATE TABLE bbb(i int CHECK(check_aaa(i)));

insert into bbb values ( 1 ); --ok
insert into bbb values ( 5 ); --new row for relation "bbb" violates check constraint "bbb_i_check"