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 tbl1 (
  t1_id text PRIMARY KEY
, qty   int NOT NULL CHECK (qty BETWEEN 1 AND 1000)  -- CHECK to prevent abuse
);

CREATE TABLE tbl2 (
  t1_id  text  NOT NULL REFERENCES tbl1              -- optional FK
, sub_id int NOT NULL                                -- separate column !
, PRIMARY KEY(t1_id, sub_id)                         -- optional PK
);

CREATE TABLE



CREATE TABLE


WITH ins1 AS (
   INSERT INTO tbl1(t1_id, qty)
   VALUES ('A1',  3)  -- input row once
   RETURNING *  -- !
   )
INSERT INTO tbl2(t1_id, sub_id)
SELECT i.t1_id, sub_id
FROM   ins1 i, generate_series(1, i.qty) sub_id
RETURNING *; -- to see result


t1_id
sub_id


A1
1

A1
2

A1
3



INSERT 0 3


-- OR add a trigger
CREATE OR REPLACE FUNCTION tbl1_insaft()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO tbl2(t1_id, sub_id)
   SELECT NEW.t1_id, sub_id
   FROM   generate_series(1, NEW.qty) sub_id;

   RETURN NULL;  -- AFTER trigger can return NULL
END
$func$;

CREATE TRIGGER insaft
AFTER INSERT ON tbl1
FOR EACH ROW EXECUTE PROCEDURE tbl1_insaft();

CREATE FUNCTION



CREATE TRIGGER


INSERT INTO tbl1(t1_id, qty) VALUES ('A2',  4);

INSERT 0 1


TABLE tbl2;


t1_id
sub_id


A1
1

A1
2

A1
3

A2
1

A2
2

A2
3

A2
4



SELECT 7