add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--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