Postgres 16

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

--this is just a mock, db<>fiddle doesn't support the pg_http extension
create function http_get(p_url text, payload jsonb)
  returns table(headers jsonb,"content" jsonb) as $f$
select jsonb_build_object('headers','headers_mock_value'),
       jsonb_build_object('path',jsonb_build_object('to',jsonb_build_object('report'
                          ,'report_'||repeat(('{a,b,c,d,e}'::text[])[floor(random()*4+1)::int],(random()*40)::int))))
$f$ language sql;

CREATE FUNCTION


create type region as enum (
  'America',
  'Europe',
  'Asia',
  'Africa'
);

create table report (
  id uuid default gen_random_uuid() primary key,
  region region,
  generated_text text
);
create table users(uid uuid default gen_random_uuid() primary key);
insert into users values (default) returning *;

create table report_user(
  id serial primary key,
  report uuid references report(id) NOT NULL,
  "user" uuid references users(uid) NOT NULL
);

create table orders(id int generated always as identity primary key,
                    "user" uuid references users(uid), 
                    region region);

create table report_stock_parameters as
select unnest(enum_range(null::region)) as region, 
       3 as min_stock_size,
       4 as restock_batch_size;

CREATE TYPE



CREATE TABLE



CREATE TABLE




uid


d043c235-d089-41df-bd91-00d03fb76468



INSERT 0 1



CREATE TABLE



CREATE TABLE



SELECT 4


select * from report_stock_parameters;


region
min_stock_size
restock_batch_size


America
3
4

Europe
3
4

Asia
3
4

Africa
3
4



SELECT 4


create function is_restock_required(p_user uuid,p_region region)
returns boolean as $f$
    select (select min_stock_size 
            from report_stock_parameters
            where region=p_region) 
          >(select count(*) --counting reports unused by the user
            from report 
            left join report_user
                   on report.id=report_user.report
                  and report.region=p_region
                  and report_user.user=p_user
            where report_user.id is null) --anti-join
$f$ language sql;

CREATE FUNCTION


create procedure fetch_some_more_reports(p_region region)as $f$
    insert into report (region,generated_text)
    select p_region, (response."content")::jsonb #>> '{path,to,report}'
    from generate_series(1,(select restock_batch_size 
                            from report_stock_parameters
                            where region=p_region))as a(g1),
         http_get('https://generative_ai_api.com/reports/please',
                   jsonb_build_object('access_token', 'eyJ0eXAiOiJKV',
                                      'throwaway', g1))as response;
$f$ language sql;

CREATE PROCEDURE


create function trgf_restock_reports()returns trigger as $f$
begin 
    call fetch_some_more_reports(new.region);
    return null;
end $f$ language plpgsql;

create trigger t_report_stock_monitor after insert on orders
for each row when (is_restock_required(new.user,new.region))
execute function trgf_restock_reports();

CREATE FUNCTION



CREATE TRIGGER


--no reports yet
select * from report;


id
region
generated_text



SELECT 0


--someone ordered one
prepare someone_orders_a_report as
insert into orders ("user",region) select uid,'Africa' from "users";
--we're assigning one to them
prepare give_them_a_report as
insert into report_user ("user",report) 
select "users".uid, report.id
from (select uid from "users" limit 1) "users", 
lateral(select id from report 
        where id not in (select report from report_user where "user"="users".uid)
        and region='Africa' 
        order by id limit 1) report
returning *;

execute someone_orders_a_report;
execute give_them_a_report;

PREPARE



PREPARE



INSERT 0 1




id
report
user


1
32b70424-efc3-4597-b444-ac9af176b6c1
d043c235-d089-41df-bd91-00d03fb76468



INSERT 0 1


--trigger fired and stocked up on reports after finding out those are running low
select report.*, array_agg(report_user.user) as used_by_users 
from report left join report_user on report.id=report_user.report
group by report.id, report.region, report.generated_text;



--Africa no longer requires a restock


id
region
generated_text
used_by_users


32b70424-efc3-4597-b444-ac9af176b6c1
Africa
report_a
{d043c235-d089-41df-bd91-00d03fb76468}

79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e
Africa
report_ddddddddddddddddddddddddddddddddddddddd
{NULL}

df0ce5b6-0f55-46c1-9ff8-351d5ab721d3
Africa
report_aaaaaaaaaaaaaaaa
{NULL}

e34e17bc-6ffb-4382-96ca-5eea762bbfcb
Africa
report_aa
{NULL}



SELECT 4


execute someone_orders_a_report;
execute give_them_a_report;

INSERT 0 1




id
report
user


2
79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e
d043c235-d089-41df-bd91-00d03fb76468



INSERT 0 1


--trigger fired but did not stock up because it's not yet needed
select report.*, array_agg(report_user.user) as used_by_users 
from report left join report_user on report.id=report_user.report
group by report.id, report.region, report.generated_text;

--Africa now  requires a refill


id
region
generated_text
used_by_users


32b70424-efc3-4597-b444-ac9af176b6c1
Africa
report_a
{d043c235-d089-41df-bd91-00d03fb76468}

79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e
Africa
report_ddddddddddddddddddddddddddddddddddddddd
{d043c235-d089-41df-bd91-00d03fb76468}

df0ce5b6-0f55-46c1-9ff8-351d5ab721d3
Africa
report_aaaaaaaaaaaaaaaa
{NULL}

e34e17bc-6ffb-4382-96ca-5eea762bbfcb
Africa
report_aa
{NULL}



SELECT 4


execute someone_orders_a_report;
execute give_them_a_report;

INSERT 0 1




id
report
user


3
08864cd0-db67-488d-b9f3-a192d83879c8
d043c235-d089-41df-bd91-00d03fb76468



INSERT 0 1


--trigger fired and stocked up on reports after finding out those are running low
select report.*, array_agg(report_user.user) as used_by_users 
from report left join report_user on report.id=report_user.report
group by report.id, report.region, report.generated_text;

--Africa still doesn't require a restock


id
region
generated_text
used_by_users


08864cd0-db67-488d-b9f3-a192d83879c8
Africa
report_ddddddddddddddddddddddddd
{d043c235-d089-41df-bd91-00d03fb76468}

32b70424-efc3-4597-b444-ac9af176b6c1
Africa
report_a
{d043c235-d089-41df-bd91-00d03fb76468}

59eca7a8-4060-4027-b3a5-481f48c3c1e9
Africa
report_dddddddd
{NULL}

70f21fba-3173-4bf7-abc7-c6959f417e58
Africa
report_bbbbbbbb
{NULL}

79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e
Africa
report_ddddddddddddddddddddddddddddddddddddddd
{d043c235-d089-41df-bd91-00d03fb76468}

8f6b43de-3b1d-4113-af87-90f8de3e1ca6
Africa
report_aaaaaaaaaaaaaaaaaaaaa
{NULL}

df0ce5b6-0f55-46c1-9ff8-351d5ab721d3
Africa
report_aaaaaaaaaaaaaaaa
{NULL}

e34e17bc-6ffb-4382-96ca-5eea762bbfcb
Africa
report_aa
{NULL}



SELECT 8

id	region	generated_text	used_by_users
32b70424-efc3-4597-b444-ac9af176b6c1	Africa	report_a	{d043c235-d089-41df-bd91-00d03fb76468}
79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e	Africa	report_ddddddddddddddddddddddddddddddddddddddd	{NULL}
df0ce5b6-0f55-46c1-9ff8-351d5ab721d3	Africa	report_aaaaaaaaaaaaaaaa	{NULL}
e34e17bc-6ffb-4382-96ca-5eea762bbfcb	Africa	report_aa	{NULL}

id	region	generated_text	used_by_users
08864cd0-db67-488d-b9f3-a192d83879c8	Africa	report_ddddddddddddddddddddddddd	{d043c235-d089-41df-bd91-00d03fb76468}
32b70424-efc3-4597-b444-ac9af176b6c1	Africa	report_a	{d043c235-d089-41df-bd91-00d03fb76468}
59eca7a8-4060-4027-b3a5-481f48c3c1e9	Africa	report_dddddddd	{NULL}
70f21fba-3173-4bf7-abc7-c6959f417e58	Africa	report_bbbbbbbb	{NULL}
79b2eb06-6ed1-4d85-9ce6-5ed55e5b238e	Africa	report_ddddddddddddddddddddddddddddddddddddddd	{d043c235-d089-41df-bd91-00d03fb76468}
8f6b43de-3b1d-4113-af87-90f8de3e1ca6	Africa	report_aaaaaaaaaaaaaaaaaaaaa	{NULL}
df0ce5b6-0f55-46c1-9ff8-351d5ab721d3	Africa	report_aaaaaaaaaaaaaaaa	{NULL}
e34e17bc-6ffb-4382-96ca-5eea762bbfcb	Africa	report_aa	{NULL}