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 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