Postgres 14

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 bookOrder (
orderID int, 
bookName varchar(30), 
required int, 
availability varchar(3)
)
insert into bookOrder 
(orderID, bookName, required, availability) values
  (1	, 'Harry Potter', 	9	, 'yes') 
, (2	, 'Twilight',   	8	, 'yes') 
, (3	, 'Bible',      	8	, 'yes')
3 rows affected

create table books (
bookName varchar(30), 
quantity int
)
insert into books (bookName, quantity) values
  ('Harry Potter', 10) 
, ('Twilight',      5) 
, ('Bible',         8)
3 rows affected

CREATE OR REPLACE FUNCTION update_bookorder_availability() 
RETURNS TRIGGER
AS $$
BEGIN

  UPDATE bookOrder bo
  SET availability = CASE 
                     WHEN bo.required <= NEW.quantity THEN 'yes' 
                     ELSE 'no' 
                     END
  WHERE bo.bookName = NEW.bookName;
  
  RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER bookorder_availability_changes
  AFTER UPDATE
  ON books
  FOR EACH ROW
  EXECUTE PROCEDURE update_bookorder_availability();
-- before update
select * from books;
select * from bookOrder;


bookname
quantity


Harry Potter
10

Twilight
5

Bible
8




orderid
bookname
required
availability


1
Harry Potter
9
yes

2
Twilight
8
yes

3
Bible
8
yes


update books set quantity = quantity;
3 rows affected

-- after update
select * from books;
select * from bookOrder;


bookname
quantity


Harry Potter
10

Twilight
5

Bible
8




orderid
bookname
required
availability


1
Harry Potter
9
yes

2
Twilight
8
no

3
Bible
8
yes