Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table people (person_id text, email_address text);
insert into people values
('001','billy@gmail.com'),
('002','bobby@gmail.com'),
('003','gregg@gmail.com'),
('004','janet@gmail.com'),
('005','boss1@gmail.com');

create table messages
( msg_id    text,
  from_addr text,
  to_addr   text[],
  cc_addr   text[],
  bcc_addr  text[] );
insert into messages values
('001','billy@gmail.com','{gregg@gmail.com,dave@gmail.com}','{boss1@gmail.com}',NULL),
('002','bobby@gmail.com','{gregg@gmail.com}',NULL,NULL),
('003','janet@gmail.com','{boss1@gmail.com}','{bobby@gmail.com}','{gregg@gmail.com}');

select  m.* 
from    messages as m 
   join people   as p 
   on p.email_address = any(m.from_addr||
                            m.to_addr  ||
                            m.cc_addr  ||
                            m.bcc_addr ) 
where p.person_id='005';

CREATE TABLE



INSERT 0 5



CREATE TABLE



INSERT 0 3




msg_id
from_addr
to_addr
cc_addr
bcc_addr


001
billy@gmail.com
{gregg@gmail.com,dave@gmail.com}
{boss1@gmail.com}
null

003
janet@gmail.com
{boss1@gmail.com}
{bobby@gmail.com}
{gregg@gmail.com}



SELECT 2


CREATE TYPE role_in_email_enum AS ENUM ('to', 'from', 'cc', 'bcc');

create table people_messages (
    msg_id text, 
    role_in_message role_in_email_enum, 
    email_address text);
insert into people_messages select msg_id, 'from', from_addr        from messages;
insert into people_messages select msg_id, 'to',   unnest(to_addr)  from messages;
insert into people_messages select msg_id, 'cc',   unnest(cc_addr)  from messages;
insert into people_messages select msg_id, 'bcc',  unnest(bcc_addr) from messages;

create index on people_messages (email_address);
create index on people (email_address);
alter table messages 
   drop column from_addr,
   drop column to_addr, 
   drop column cc_addr,
   drop column bcc_addr,
   add column email_body text default 'Hello from e-mail body.';


table messages;
table people;
table people_messages;

CREATE TYPE



CREATE TABLE



INSERT 0 3



INSERT 0 4



INSERT 0 2



INSERT 0 1



CREATE INDEX



CREATE INDEX



ALTER TABLE




msg_id
email_body


001
Hello from e-mail body.

002
Hello from e-mail body.

003
Hello from e-mail body.



SELECT 3




person_id
email_address


001
billy@gmail.com

002
bobby@gmail.com

003
gregg@gmail.com

004
janet@gmail.com

005
boss1@gmail.com



SELECT 5




msg_id
role_in_message
email_address


001
from
billy@gmail.com

002
from
bobby@gmail.com

003
from
janet@gmail.com

001
to
gregg@gmail.com

001
to
dave@gmail.com

002
to
gregg@gmail.com

003
to
boss1@gmail.com

001
cc
boss1@gmail.com

003
cc
bobby@gmail.com

003
bcc
gregg@gmail.com



SELECT 10


select  p.email_address, 
        pm.role_in_message, 
        m.* 
from    messages as m 
   join people_messages as pm
   using(msg_id)
   join people   as p 
   using(email_address)
where p.person_id='005';


email_address
role_in_message
msg_id
email_body


boss1@gmail.com
cc
001
Hello from e-mail body.

boss1@gmail.com
to
003
Hello from e-mail body.



SELECT 2

msg_id	from_addr	to_addr	cc_addr	bcc_addr
001	billy@gmail.com	{gregg@gmail.com,dave@gmail.com}	{boss1@gmail.com}	null
003	janet@gmail.com	{boss1@gmail.com}	{bobby@gmail.com}	{gregg@gmail.com}

msg_id	email_body
001	Hello from e-mail body.
002	Hello from e-mail body.
003	Hello from e-mail body.

person_id	email_address
001	billy@gmail.com
002	bobby@gmail.com
003	gregg@gmail.com
004	janet@gmail.com
005	boss1@gmail.com

msg_id	role_in_message	email_address
001	from	billy@gmail.com
002	from	bobby@gmail.com
003	from	janet@gmail.com
001	to	gregg@gmail.com
001	to	dave@gmail.com
002	to	gregg@gmail.com
003	to	boss1@gmail.com
001	cc	boss1@gmail.com
003	cc	bobby@gmail.com
003	bcc	gregg@gmail.com