Postgres 14

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 ins_tbl(
  id int GENERATED ALWAYS AS IDENTITY
, name text
);

CREATE VIEW v_ins_tbl AS
SELECT *, NULL::int AS site_id
FROM   ins_tbl;

-- trigger func
CREATE OR REPLACE FUNCTION trg_ins_tbl()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE NEW.site_id
   WHEN 1 THEN  -- cascade insert
      INSERT INTO ins_tbl (name) VALUES (NEW.name);
      RETURN NEW;
   WHEN 2 THEN  -- propagate insert, but stop there
      INSERT INTO ins_tbl (name) VALUES (NEW.name);
      RETURN NULL;      
   WHEN 0 THEN  -- cancel silently
      RETURN NULL;
   ELSE  -- raise error
      RAISE EXCEPTION 'Unexpected site_id: %', NEW.site_id;
   END CASE;
END
$func$;

-- trigger
CREATE TRIGGER trg_insbef_ins_tbl
INSTEAD OF INSERT ON v_ins_tbl
FOR EACH ROW
EXECUTE FUNCTION trg_ins_tbl();

CREATE TABLE

CREATE VIEW

CREATE FUNCTION

CREATE TRIGGER

-- init
INSERT INTO ins_tbl(id, name) OVERRIDING SYSTEM VALUE VALUES (0, 'foo');
TABLE ins_tbl;
TABLE v_ins_tbl;

INSERT 0 1

id
name

0
foo

SELECT 1

id
name
site_id

0
foo
null

SELECT 1

-- missing or unexpected site_id raises custom exception
INSERT INTO v_ins_tbl(name) VALUES ('xxx');
INSERT INTO v_ins_tbl(name, site_id) VALUES ('xxx', 7);

ERROR:  Unexpected site_id: <NULL>
CONTEXT:  PL/pgSQL function trg_ins_tbl() line 13 at RAISE

-- propagated normally
INSERT INTO v_ins_tbl(name, site_id) VALUES ('bar', 1);

INSERT 0 1

-- propagated normally, but "id" is always ignored (default IDENTITY used)
INSERT INTO v_ins_tbl(id,name, site_id) VALUES (1, 'baz', 1);

INSERT 0 1

-- INSERT is propagated, but not reported and Postgres stops there
INSERT INTO v_ins_tbl(name, site_id) VALUES ('bam', 2);

INSERT 0 0

-- INSERT cancelled silently
INSERT INTO v_ins_tbl(name, site_id) VALUES ('xxx', 0);

INSERT 0 0

TABLE ins_tbl;
TABLE v_ins_tbl;

id
name

0
foo

1
bar

2
baz

3
bam

SELECT 4

id
name
site_id

0
foo
null

1
bar
null

2
baz
null

3
bam
null

SELECT 4

insert into v_ins_tbl (id, name, site_id) values (default, 'test', 1);

INSERT 0 1

TABLE ins_tbl;

id
name

0
foo

1
bar

2
baz

3
bam

4
test

SELECT 5