Challenge 2 of https://yrashk.com/blog/2023/04/04/make-postgres-an-application-server-gamified/ based on kartikynwa version but without plpgsql

challenge_2.sql

create table if not exists users
(
    id        int primary key generated always as identity,
    name      text not null
);

create table if not exists apikeys
(
    userid int references users(id),
    apikey text not null
);

create type userrole as enum ('user', 'admin');

create table if not exists userroles
(
    userid int references users(id) unique,
    role userrole not null
);

insert into users (name) values ('eve'), ('adam');
insert into userroles (userid, role) values (1, 'admin'), (2, 'user');
insert into apikeys (userid, apikey)
    values (1, 'ihie1HaiG3vaeyiez1ah'), (2, 'loh7eeDishaip4PheKoh');

create table if not exists motd
(
    id        int primary key generated always as identity,
    content   text,
    posted_at timestamp default now()
);

create or replace function show_motd() returns setof omni_httpd.http_response as $$
select
    omni_httpd.http_response('Posted at ' || posted_at || E'\n' || content)
from
    motd
order by
    posted_at desc
limit 1;
$$ language sql;

create or replace function update_motd(request omni_httpd.http_request) returns omni_httpd.http_response as $$
with inserts as (
    with is_authorized as (
        select 1 from apikeys ak join userroles ur on ak.userid = ur.userid
        where ak.apikey = omni_httpd.http_header_get(request.headers, 'authorization')
            and ur.role = 'admin'
    )
    insert into motd (content)
    select convert_from(request.body, 'UTF8') from is_authorized
    returning 1
)
select case when (select count(*) from inserts) <> 0
    then omni_httpd.http_response(status => 201)
    else omni_httpd.http_response(status => 403, body => 'Unauthorized')
end
$$ language sql;


create or replace function no_motd() returns setof omni_httpd.http_response as $$
select omni_httpd.http_response('No MOTD');
$$ language sql;

update omni_httpd.handlers
set
    query = (select
                 -- 
                 omni_httpd.cascading_query(name, query order by priority desc nulls last)
             from
                 (values
                      ('show', $$select show_motd() from request where request.method = 'GET'$$, 1),
                      ('update', $$select update_motd(request.*) from request where request.method = 'POST'$$, 1),
                      ('fallback', $$select no_motd() from request where request.method = 'GET'$$,
                       0)) handlers(name, query, priority));

-- ➜  ~ curl -v --data "new motd" http://localhost:8080       
-- *   Trying 127.0.0.1:8080...
-- * Connected to localhost (127.0.0.1) port 8080 (#0)
-- > POST / HTTP/1.1
-- > Host: localhost:8080
-- > User-Agent: curl/7.86.0
-- > Accept: */*
-- > Content-Length: 8
-- > Content-Type: application/x-www-form-urlencoded
-- > 
-- * Mark bundle as not supporting multiuse
-- < HTTP/1.1 403 OK
-- < Connection: keep-alive
-- < Server: omni_httpd-0.1
-- < content-type: text/plain; charset=utf-8
-- < transfer-encoding: chunked
-- < 
-- * Connection #0 to host localhost left intact
-- Unauthorized%
--
--
-- ➜  ~ curl -v --data "new motd" --header 'authorization: inexistent_token' http://localhost:8080
-- *   Trying 127.0.0.1:8080...
-- * Connected to localhost (127.0.0.1) port 8080 (#0)
-- > POST / HTTP/1.1
-- > Host: localhost:8080
-- > User-Agent: curl/7.86.0
-- > Accept: */*
-- > authorization: inexistent_token
-- > Content-Length: 8
-- > Content-Type: application/x-www-form-urlencoded
-- > 
-- * Mark bundle as not supporting multiuse
-- < HTTP/1.1 403 OK
-- < Connection: keep-alive
-- < Server: omni_httpd-0.1
-- < content-type: text/plain; charset=utf-8
-- < transfer-encoding: chunked
-- < 
-- * Connection #0 to host localhost left intact
-- Unauthorized%
--
--
-- ➜  ~ curl -v --data "MOTD for Omnigre Challenge 2" --header 'authorization: ihie1HaiG3vaeyiez1ah' http://localhost:8080
-- *   Trying 127.0.0.1:8080...
-- * Connected to localhost (127.0.0.1) port 8080 (#0)
-- > POST / HTTP/1.1
-- > Host: localhost:8080
-- > User-Agent: curl/7.86.0
-- > Accept: */*
-- > authorization: ihie1HaiG3vaeyiez1ah
-- > Content-Length: 28
-- > Content-Type: application/x-www-form-urlencoded
-- > 
-- * Mark bundle as not supporting multiuse
-- < HTTP/1.1 201 OK
-- < Connection: keep-alive
-- < Server: omni_httpd-0.1
-- < transfer-encoding: chunked
-- < 
-- * Connection #0 to host localhost left intact
--
--
-- ➜  ~ curl http://127.0.0.1:8080
-- Posted at 2023-04-07 19:57:17.678496
-- MOTD for Omnigre Challenge 2%