Postgres 15

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

CREATE TABLE prices (
  prices_id serial PRIMARY KEY
, id int
, price int
, eff_from date
, eff_to date
);

CREATE TABLE


CREATE OR REPLACE FUNCTION prices_etl()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO NEW.eff_to
          p.eff_from - 1
   FROM   prices P
   WHERE  p.id = NEW.id
   AND    p.eff_from > NEW.eff_from
   ORDER  BY p.eff_from
   LIMIT  1;
   
   IF NOT FOUND THEN
      NEW.eff_to = 'infinity';  -- better than arbitrary year 
   END IF;
   
   RETURN NEW;  -- !
END
$func$;


CREATE OR REPLACE TRIGGER before_insert_prices
BEFORE INSERT ON prices
FOR EACH ROW
WHEN (NEW.eff_to IS NULL)            
EXECUTE FUNCTION prices_etl();

CREATE FUNCTION



CREATE TRIGGER


-- Single-row inserts obviously can't see later rows
INSERT INTO prices(id, price, eff_from) VALUES
  (1, 100, '2017-01-12');

INSERT INTO prices(id, price, eff_from) VALUES
  (1, 150, '2017-02-09');

INSERT INTO prices(id, price, eff_from) VALUES
  (1, 125, '2017-01-27');

INSERT 0 1



INSERT 0 1



INSERT 0 1


-- In multi-row INSERTs, *earlier* rows are also visible
-- But not later rows are still invisible
INSERT INTO prices(id, price, eff_from) VALUES
  (2, 100, '2017-01-12')
, (2, 150, '2017-02-09')
, (2, 125, '2017-01-27')
RETURNING *;  -- NOTE how RETURNING already shows effects of BEFORE trigger!


prices_id
id
price
eff_from
eff_to


4
2
100
2017-01-12
infinity

5
2
150
2017-02-09
infinity

6
2
125
2017-01-27
2017-02-08



INSERT 0 3


TABLE prices
ORDER BY id, eff_from;


prices_id
id
price
eff_from
eff_to


1
1
100
2017-01-12
infinity

3
1
125
2017-01-27
2017-02-08

2
1
150
2017-02-09
infinity

4
2
100
2017-01-12
infinity

6
2
125
2017-01-27
2017-02-08

5
2
150
2017-02-09
infinity



SELECT 6

prices_id	id	price	eff_from	eff_to
4	2	100	2017-01-12	infinity
5	2	150	2017-02-09	infinity
6	2	125	2017-01-27	2017-02-08