Postgres 11

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 product (
  product_id serial PRIMARY KEY,
  product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
  purchase_id serial PRIMARY KEY,
  product_id  int REFERENCES product,
  when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase    pu
LEFT  JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

CREATE TABLE

CREATE TABLE

CREATE VIEW

INSERT 0 1

-- insert even when produkt_name not found
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase(product_id, when_bought)
   SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
   RETURNING purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

CREATE FUNCTION

CREATE TRIGGER

TABLE product;

product_id
product_name

1
foo

SELECT 1

INSERT INTO purchaseView(product_name, when_bought) VALUES ('foo', NOW()) RETURNING *;

purchase_id
product_name
when_bought

1
foo
2022-12-26

INSERT 0 1

-- product nonexistent!
INSERT INTO purchaseView(product_name, when_bought) VALUES ('bar', NOW()) RETURNING *;

purchase_id
product_name
when_bought

2
bar
2022-12-26

INSERT 0 1

TABLE purchase;  -- row was still inserted!

purchase_id
product_id
when_bought

1
1
2022-12-26

2
null
2022-12-26

SELECT 2

-- skip when product_name not found
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,     when_bought)
   SELECT pr.product_id, NEW.when_bought
   FROM   product pr
   WHERE  pr.product_name = NEW.product_name
   RETURNING pu.purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   IF NOT FOUND THEN
      RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
   END IF;
   
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE FUNCTION

INSERT INTO purchaseView(product_name, when_bought) VALUES ('bar', NOW()) RETURNING *;

purchase_id
product_name
when_bought

null
bar
2022-12-26

INSERT 0 1

TABLE purchase;  -- row was still inserted!

purchase_id
product_id
when_bought

1
1
2022-12-26

2
null
2022-12-26

SELECT 2

-- more columns, more lookups ...
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,   when_bought)  -- more columns?
   SELECT pr.product_id, i.when_bought   -- more columns?
   FROM  (SELECT NEW.*) i
   JOIN   product pr USING (product_name)
   -- JOIN   tbl1 t2 USING (t2_name)     -- more lookups?
    RETURNING pu.purchase_id             -- more columns?
   INTO   NEW.purchase_id;               -- more columns?

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE FUNCTION

INSERT INTO purchaseView(product_name, when_bought) VALUES ('foo', NOW()) RETURNING *;

purchase_id
product_name
when_bought

3
foo
2022-12-26

INSERT 0 1

INSERT INTO purchaseView(product_name, when_bought) VALUES ('bar', NOW()) RETURNING *;  -- skipped

purchase_id
product_name
when_bought

null
bar
2022-12-26

INSERT 0 1

TABLE purchase;

purchase_id
product_id
when_bought

1
1
2022-12-26

2
null
2022-12-26

3
1
2022-12-26

SELECT 3