I have a view that joins several tables and want to create unconditional INSERT RETURNING rule for it. I succeeded by specifying the RETURNING clause for the first INSERT in the rule, casting NULL for columns that are not present in that table to the correct type:
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER REFERENCES a, surname TEXT);
CREATE VIEW j AS (SELECT
a.id,
a.name, b.surname FROM a NATURAL JOIN b);
CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name, NULL::text;
INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) );
Is there a way to make RETURNING return all view columns? If not, I'd like to submit a documentation patch that clarifies the behaviour of CREATE RULE for INSERT RETURNING in that case.
--
cheers,
Sava Chankov