How do I make this Postgres view that performs a join updatable?

Question

I've got two tables, products and subscriptions:

CREATE TABLE products (
    id bigint NOT NULL,
    title character varying(75),
    description text,
    manufacturer_id bigint,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    mpn text,
    visible boolean DEFAULT false NOT NULL
);

CREATE TABLE subscriptions (
    id bigint NOT NULL,
    product_id bigint NOT NULL,
    user_id bigint NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

In my application I usually need to know the number of subscribers for a product, and it is tricky sprinkling this logic in the application in all the correct places. So I would like to replace the products table with a view that already includes that information. So I did this:

ALTER TABLE ONLY products
  RENAME TO products_raw;

CREATE VIEW products AS
  SELECT products_raw.*, COALESCE(a.subscriptions_count, 0) AS subscriptions_count
  FROM products_raw
  LEFT OUTER JOIN (
    SELECT b.product_id, COUNT(*) subscriptions_count
    FROM   subscriptions b
    GROUP BY b.product_id
  ) a ON a.product_id = products_raw.id;

However the products view is not auto-updatable due to the JOIN - I would like any INSERT/UPDATE/DELETE actions to instead be performed on the products_raw table, ignoring the virtual subscriptions_count when applicable.

This is the first time I've created a view or rule, but I attempted this rule:

CREATE RULE products_insert_rule AS ON INSERT TO products DO INSTEAD
  INSERT INTO products_raw VALUES(NEW.*);

However Postgres doesn't like that:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 13:         INSERT INTO products_raw VALUES(NEW.*);
                                                 ^

I also tried writing an INSTEAD OF INSERT trigger:

CREATE FUNCTION products_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO products_raw VALUES (NEW.*);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

but got essentially the same error message:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO products_raw VALUES (NEW.*)
                                         ^
QUERY:  INSERT INTO products_raw VALUES (NEW.*)
CONTEXT:  PL/pgSQL function products_insert() line 3 at SQL statement
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

I'm using Postgres 9.4. Any tips would be appreciated.

Edit: I got a bit closer with this trigger, but it's still erroring due to me not being sure how to cast the products_raw rowtype to a products. I also don't like the direction this is going with having to repeat default values (including the id sequence)...

CREATE FUNCTION products_insert() RETURNS trigger AS $$
DECLARE
    p products_raw%ROWTYPE;
BEGIN
  IF p.id IS NULL THEN
    p.id = NEXTVAL('products_id_seq');
  END IF;
  IF p.visible IS NULL THEN
    p.visible = false;
  END IF;
  INSERT INTO products_raw VALUES(p.*);

  RETURN p;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

The error I get with the above trigger is

PG::DatatypeMismatch: ERROR:  returned row structure does not match the structure of the triggering table
DETAIL:  Number of returned columns (8) does not match expected column count (9).
CONTEXT:  PL/pgSQL function products_insert() during function exit
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

Answer 1

You can't use NEW.*, because the view has columns that don't exist in the underlying table. You have to explicitly list out the columns you want to add. As people explained above, the reason this didn't work is that you did not provide a default. You might want to consider using COALESCE() for this purpose.