create table accounts (
account_id bigint primary key
, name varchar not null
);
insert into accounts(account_id, name)
select nextval('s_id') as account_id
, t.name
from (
select distinct sender as name from messages
union
select distinct receiver as name from messages) t;
alter table messages
add column sender_id bigint
, add column receiver_id bigint;
update messages set
sender_id = (select account_id from accounts a where name = sender)
, receiver_id = (select account_id from accounts a where name = receiver);
alter table messages
add constraint msg_sender_id_fk foreign key (sender_id) references accounts(account_id)
, add constraint msg_receiver_id_fk foreign key (receiver_id) references accounts(account_id)
, drop column sender
, drop column receiver;
create or replace function send_message(p_receiver varchar, p_contents varchar default null) returns bigint as
$send_message$
insert into messages(message_id, sender_id, receiver_id, contents)
values (
nextval('s_id')
, (select account_id from accounts where name = user)
, (select account_id from accounts where name = p_receiver)
, p_contents)
returning message_id;
$send_message$
language sql
security definer;
create or replace function get_new_messages(p_receiver varchar) returns table (
message_id bigint
, sender varchar
, contents varchar
) as
$get_new_messages$
select message_id, acc_sender.name, contents
from messages msg
, accounts acc_sender
, accounts acc_receiver
where msg.sender_id = acc_sender.account_id
and msg.receiver_id = acc_receiver.account_id
and acc_receiver.name = p_receiver
and msg.is_read = false
order by creation_time;
$get_new_messages$
language sql
security definer;
create or replace function mark_as_read(p_receiver varchar, p_message_id bigint) returns void as
$mark_as_read$
update messages set is_read = true
where receiver_id in (select account_id from accounts where name = p_receiver)
and message_id = p_message_id;
$mark_as_read$
language sql
security definer;