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_set_all_eff_to()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE prices p
   SET    eff_to = COALESCE(p1.next_eff_to, 'infinity')
   FROM   news n
   LEFT   JOIN LATERAL (
      SELECT p1.eff_from - 1 AS next_eff_to
      FROM   prices p1
      WHERE  p1.id = n.id
      AND    p1.eff_from > n.eff_from
      ORDER  BY p1.eff_from
      LIMIT  1
      ) p1 ON true
   WHERE  n.eff_to IS NULL
   AND    p.prices_id = n.prices_id;

   RETURN NULL;  -- for statement-level trigger
END
$func$;

CREATE TRIGGER after_insert_prices
AFTER INSERT ON prices
REFERENCING NEW TABLE AS news  -- transition table
FOR EACH STATEMENT
EXECUTE FUNCTION prices_set_all_eff_to();

CREATE FUNCTION



CREATE TRIGGER


-- multi-row (!) inserts work as desired: all rows are visible to all
INSERT INTO prices(id, price, eff_from) VALUES
  (1, 100, '2017-01-12')
, (1, 150, '2017-02-09')
, (1, 125, '2017-01-27')
RETURNING *;  -- NOTE how RETURNING does not show effects of AFTER trigger, yet!


prices_id
id
price
eff_from
eff_to


1
1
100
2017-01-12
null

2
1
150
2017-02-09
null

3
1
125
2017-01-27
null



INSERT 0 3


-- ... even when mixing with pre-existing rows
INSERT INTO prices(id, price, eff_from) VALUES
  (1, 200, '2017-01-13')
, (1, 250, '2017-02-08')
, (1, 225, '2017-01-28')
;

INSERT 0 3


-- But pre-existing rows are not changed!
INSERT INTO prices(id, price, eff_from) VALUES
 (1, 350, '2017-02-10')  -- voids old "infinity"
;

INSERT 0 1


-- But single-row INSERTs have no way of anticipating later rows:
INSERT INTO prices(id, price, eff_from) VALUES
  (2, 100, '2017-01-12');

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

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

INSERT 0 1



INSERT 0 1



INSERT 0 1


TABLE prices
ORDER BY id, eff_from;


prices_id
id
price
eff_from
eff_to


1
1
100
2017-01-12
2017-01-26

4
1
200
2017-01-13
2017-01-26

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

6
1
225
2017-01-28
2017-02-07

5
1
250
2017-02-08
2017-02-08

2
1
150
2017-02-09
infinity

7
1
350
2017-02-10
infinity

8
2
100
2017-01-12
infinity

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

9
2
150
2017-02-09
infinity



SELECT 10

prices_id	id	price	eff_from	eff_to
1	1	100	2017-01-12	null
2	1	150	2017-02-09	null
3	1	125	2017-01-27	null

prices_id	id	price	eff_from	eff_to
1	1	100	2017-01-12	2017-01-26
4	1	200	2017-01-13	2017-01-26
3	1	125	2017-01-27	2017-02-08
6	1	225	2017-01-28	2017-02-07
5	1	250	2017-02-08	2017-02-08
2	1	150	2017-02-09	infinity
7	1	350	2017-02-10	infinity
8	2	100	2017-01-12	infinity
10	2	125	2017-01-27	2017-02-08
9	2	150	2017-02-09	infinity