Re: INSERT RETURNING rule for joined view - Mailing list pgsql-general

Sava Chankov <sava.chankov@gmail.com> writes:
> Is there a way to make RETURNING return all view columns?

Something like

CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
  INSERT INTO a (id,name) VALUES (NEW.id, NEW.name);
  INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname)
    RETURNING id, (SELECT name FROM a WHERE id = b.id) as name, surname
);

This only really works if the insert specifies "id" explicitly, which is
not amazingly desirable.  That's not the fault of the RETURNING though,
but of the repeat reference to NEW.id which might be a volatile
expression (ie, nextval()).  In some cases it's okay to hack around that
by using currval() the second time, but that just trades off one
unexpected behavior for a different one ...

            regards, tom lane