INSERT RETURNING rule for joined view

From: Sava Chankov <sava(dot)chankov(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INSERT RETURNING rule for joined view
Date: 2009-06-01 17:54:41
Message-ID: 8834b7bc0906011054p7ff6888clb51aefc0facb7538@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Douglas Alan 2009-06-01 18:20:38 How can I manually alter the statistics for a column?
Previous Message Alvaro Herrera 2009-06-01 16:04:52 Re: pg_dump & table space