I like the idea of adding an INSERT ... RETURNING capability, per Philip Warner's suggestion of about a year ago (http://fts.postgresql.org/db/mw/msg.html?mid=68704). We did not figure out what to do if the INSERT operation is rewritten by a rule, but I have an idea about that. ISTM that to support INSERT RETURNING on a view, we should require an ON INSERT DO INSTEAD rule to end with a SELECT, and it is the results of that SELECT that are used to compute the RETURNING values. This gives the author of a view the ability and responsibility to determine what is seen when an INSERT RETURNING is done into the view. It further seems a good idea to mark a SELECT intended for this purpose in a special way, to flag that it's only needed to support RETURNING and isn't a fundamental part of the rule. This would allow us to suppress execution of the SELECT when the original query is a plain INSERT and not INSERT RETURNING. I suggest that we do this by using "RETURNS" instead of "SELECT" --- the rest of the query is just like a select, only the initial keyword is different. So you'd write something like CREATE RULE foorule AS ON INSERT TO fooview DO INSTEAD ( insert into underlying tables; RETURNS a,b,c FROM ... ); If you don't provide the RETURNS query, the rule will still work for simple inserts, but an error would be raised for INSERT RETURNING. When you do provide RETURNS, it's only executed if the rule is used to rewrite INSERT RETURNING. The output columns of the RETURNS query have to match the column datatypes of the table (view) the rule is attached to. While this all seems good at first glance, I am wondering just how useful it really would be in practice. The problem is: how do you know which rows to return in the RETURNS query? If you don't qualify the selection then you'll get all the rows in the view, which is surely not what you want. You could restrict the select with clauses like "WHERE col1 = NEW.col1", but this is not necessarily going to be efficient, and what's worse it only works for columns that are supplied by the initial insert into the view. For example, suppose an underlying table has a SERIAL primary key that's generated on the fly when you insert to it. The RETURNS query has no way to know what that serial number is, and so no way to select the right row. It seems like the rule author is up against the very same problem that we wanted INSERT RETURNING to solve. So I'm still baffled, unless someone sees a way around that problem. Could we get away with restricting INSERT RETURNING to work only on inserts directly to tables (no ON INSERT DO INSTEAD allowed)? Or is that too much of a kluge? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
