Postgres 10

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 sample_tbl ( id int , created_date date, current_order int )

CREATE TABLE


CREATE OR REPLACE FUNCTION test_func()
RETURNS TRIGGER AS 
$$
DECLARE cnt INTEGER;
BEGIN
    select COUNT(*) INTO cnt FROM sample_tbl WHERE id = NEW.id 
      AND created_date = NEW.created_date;
    NEW.current_order := cnt + 1; -- I want to set value of sample_tbl.current_order automatically
RETURN NEW;
END 
$$ LANGUAGE plpgsql;


CREATE TRIGGER test_trigger
BEFORE INSERT
  ON sample_tbl
FOR EACH ROW
EXECUTE PROCEDURE test_func();

CREATE FUNCTION



CREATE TRIGGER


INSERT INTO sample_tbl(id,created_date) VALUES(1,current_date);
INSERT INTO sample_tbl(id,created_date) VALUES(1,current_date);
INSERT INTO sample_tbl(id,created_date) VALUES(2,current_date);

INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from sample_tbl;


id
created_date
current_order


1
2024-02-08
1

1
2024-02-08
2

2
2024-02-08
1



SELECT 3


select t.*, row_number() OVER ( partition by  id,created_date order by id )
FROM sample_tbl t;


id
created_date
current_order
row_number


1
2024-02-08
1
1

1
2024-02-08
2
2

2
2024-02-08
1
1



SELECT 3