create insertion rule on view returning id

Question

I have a table private.products and a view public.products representing that table. And I want to create entries in private.products while inserting into view, it all works right, except that I also what to return the inserted id:

CREATE RULE insert_product 
AS ON INSERT TO public.products DO INSTEAD 
INSERT INTO private.products (name) VALUES (new.name) RETURNING products.id;
ERROR:  RETURNING list has too few entries

What am I doing wrong?

Answer 1

The wording of the relevant documentation suggests that you must return the same number of columns as the products view actually has. This is because the rule is not a query itself; it is a rule for rewriting queries. The queries may have their own RETURNING clauses, which could refer to any of the view's columns. Your rule can rewrite those clauses, but only if you specify what to rewrite each returned column into.