Postgres 13

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 measurements (   -- mothership of trigger
  measurement_id serial NOT NULL PRIMARY KEY
, measurement_size_in_bytes integer NOT NULL
);

CREATE TABLE events (         -- target of trigger
  event_id serial NOT NULL PRIMARY KEY
, measurement_id integer NOT NULL
, event_index_start integer NOT NULL
, event_index_end integer NOT NULL
);

-- generic trigger function
CREATE OR REPLACE FUNCTION insaft_function()
  RETURNS trigger
  LANGUAGE plpgsql AS 
$func$
DECLARE
   m int[];
BEGIN
-- RAISE NOTICE '%', TG_ARGV[0];

   FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]  -- or bigint[] ?
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end) 
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;

   RETURN NULL;  -- result ignored since this is used in AFTER trigger
END 
$func$;


-- function automating trigger creation
CREATE OR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format($$
      DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;
      CREATE TRIGGER insaft_%1$s_ids
      AFTER INSERT ON %1$s
      FOR EACH ROW EXECUTE PROCEDURE insaft_function(%2$L)$$
                , _tbl
                , translate(_arg0, '[]', '{}')
      );
END 
$func$;
SELECT f_create_my_trigger('measurements', '[[1,2],[3,4]]');
INSERT INTO measurements (measurement_size_in_bytes) values (5);
TABLE events;


f_create_my_trigger





1 rows affected



event_id
measurement_id
event_index_start
event_index_end


1
1
1
2

2
1
3
4


SELECT f_create_my_trigger('measurements', '{{5,6},{7,8}}');
INSERT INTO measurements (measurement_size_in_bytes) values (8);
TABLE events;


f_create_my_trigger





1 rows affected



event_id
measurement_id
event_index_start
event_index_end


1
1
1
2

2
1
3
4

3
2
5
6

4
2
7
8