Postgres 16

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 table1 (
  id_t1 int
, product_id int
);

CREATE TABLE table2 (
  id_t2 int
, product_id int
, name_1 text
);

CREATE TABLE table3 (
  id_t3 int
, id_t1 int
, name_1 text
, bool_t2 bool
);

CREATE TABLE test (
  field1 int
, field2 text
);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO table2 (id_t2, product_id, name_1) VALUES
  (1, 1, 'name1')
, (2, 2, 'name2')
RETURNING *;


id_t2
product_id
name_1


1
1
name1

2
2
name2



INSERT 0 2


-- trigger func & trigger
CREATE OR REPLACE FUNCTION my_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO table3
         (   id_t3,     id_t1,    name_1, bool_t2)
   SELECT t2.id_t2, NEW.id_t1, t2.name_1, true
   FROM   table2 t2
   WHERE  t2.product_id = NEW.product_id;

   INSERT INTO test
         (  field1, field2)
   SELECT t2.id_t2, 'INSERT ...'
   FROM   table2 t2
   WHERE  t2.product_id = NEW.product_id;

   RETURN NULL;
END
$func$;

CREATE TRIGGER my_trigger
AFTER INSERT ON table1
FOR EACH ROW
EXECUTE FUNCTION my_trigger();

CREATE FUNCTION



CREATE TRIGGER


INSERT INTO table1 (id_t1, product_id) VALUES (1, 1) RETURNING *;


id_t1
product_id


1
1



INSERT 0 1


TABLE table3;
TABLE test;


id_t3
id_t1
name_1
bool_t2


1
1
name1
t



SELECT 1




field1
field2


1
INSERT ...



SELECT 1


-- same trigger with CTEs
CREATE OR REPLACE FUNCTION my_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   WITH t2 AS (
      SELECT t.id_t2, t.name_1
      FROM   table2 t
      WHERE  t.product_id = NEW.product_id
      )
   , ins_t3 AS (
      INSERT INTO table3
            (   id_t3,     id_t1,    name_1, bool_t2)
      SELECT t2.id_t2, NEW.id_t1, t2.name_1, true
      FROM   t2
      )
   INSERT INTO test
         (  field1, field2)
   SELECT t2.id_t2, 'INSERT ...'
   FROM   t2;

   RETURN NULL;
END
$func$;

CREATE FUNCTION


INSERT INTO table1 (id_t1, product_id) VALUES (2, 2) RETURNING *;


id_t1
product_id


2
2



INSERT 0 1


TABLE table3;
TABLE test;


id_t3
id_t1
name_1
bool_t2


1
1
name1
t

2
2
name2
t



SELECT 2




field1
field2


1
INSERT ...

2
INSERT ...



SELECT 2