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?.
create table items (
-- Use bigserial because 2 billion is smaller than you think
id bigserial primary key,
-- Ranges are simpler to work and easier to index with than from/to.
when_available daterange not null
);
-- Pluralize table names to avoid conflicts with columns and keywords.
create table orders (
id bigserial primary key,
ordered_on date not null
);
-- Put join table names in alphabetical order to be predictable.
create table item_orders (
order_id bigint not null references orders,
item_id bigint not null references items
);
create function check_item_order_is_valid()
returns trigger
language 'plpgsql'
as $body$
declare
item_is_available boolean;
begin
select
items.when_available @> orders.ordered_on into item_is_available
from item_orders
join items on items.id = NEW.order_id
join orders on orders.id = NEW.item_id;
if( not item_is_available) then
raise exception 'Item #% is not available for order #%',
NEW.item_id, NEW.order_id;
end if;
return new;
end
$body$
create trigger check_item_orders
before insert or update
on item_orders
for each row
execute function check_item_order_is_valid();
insert into items(id, when_available) values
(1, '[2021-01-01, 2021-02-01)'), (2, '[2021-02-01, 2021-03-01)');
2 rows affected
insert into orders (id, ordered_on) values
(1, '2021-01-05');
1 rows affected
insert into item_orders (item_id, order_id) values (1, 1);
1 rows affected
insert into item_orders (item_id, order_id) values (1, 2);
ERROR: Item #1 is not available for order #2
CONTEXT: PL/pgSQL function check_item_order_is_valid() line 12 at RAISE