Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


-- create tables
create table kontakte(valid_email text primary key); 
   
create table registrations(id    integer generated always as identity
                                 primary key
                  ,name  text
                  ,email text
                  ,constraint valid_user_email 
                              foreign key(email) 
                              references kontakte(valid_email) 
                  );

CREATE TABLE



CREATE TABLE


-- populate pre-authorized emails.

insert into kontakte(valid_email) 
  select 've_' || ltrim(to_char(n,'009')) || '@some.email_server'
    from generate_series(1,7) gs(n);
   
select * 
  from kontakte;

INSERT 0 7




valid_email


ve_001@some.email_server

ve_002@some.email_server

ve_003@some.email_server

ve_004@some.email_server

ve_005@some.email_server

ve_006@some.email_server

ve_007@some.email_server



SELECT 7


-- simple insert of invalid email
insert into registrations(name, email) values ('I''m bad', 'im_bad@unknown_email_server');

ERROR:  insert or update on table "registrations" violates foreign key constraint "valid_user_email"
DETAIL:  Key (email)=(im_bad@unknown_email_server) is not present in table "kontakte".


-- simple insert of valid email
insert into registrations(name, email) values ('I''m Good','ve_001@some.email_server');

INSERT 0 1


-- Custom code to intercert error and supply custom message. 
-- Replace with  TRY ... CATCH block
-- BUT a valid email

do $$
declare
  constraint_violated  text;
  bad_email_constraint text = 'valid_user_email';
begin
       insert into registrations(name, email)
       values ('I''m not Good','ve_101@some.email_server');
exception
  when foreign_key_violation then
    get stacked diagnostics constraint_violated = constraint_name;
    if constraint_violated = bad_email_constraint then
      raise 'Unacceptable email. You are not allowed to register.';
    else
      raise;
    end if;
end $$ 

ERROR:  Unacceptable email. You are not allowed to register.
CONTEXT:  PL/pgSQL function inline_code_block line 12 at RAISE


-- Custom code to intercert error and supply custom message. 
-- Replace with  TRY ... CATCH block
-- BUT a valid email

do $$
declare
  constraint_violated  text;
  bad_email_constraint text = 'valid_user_email';
begin
       insert into registrations(name, email)
       values ('I''m Good too','ve_004@some.email_server');
exception
  when foreign_key_violation then
    get stacked diagnostics constraint_violated = constraint_name;
    if constraint_violated = bad_email_constraint then
      raise 'Unacceptable email. You are not allowed to register.';
    else
      raise;
    end if;
end $$ 

DO


select * 
  from registrations;


id
name
email


2
I'm Good
ve_001@some.email_server

4
I'm Good too
ve_004@some.email_server



SELECT 2
id	name	email
2	I'm Good	ve_001@some.email_server
4	I'm Good too	ve_004@some.email_server