Re: Incomplete idea about views and INSERT...RETURNING

Tom Lane wrote:
> 
> 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?

Isn't it likely that the person writing the RULE would want to internally use an
INSERT ... RETURNING query and that the RETURNS ... should either use values from
that, or use a SELECT clause keyed on values from that?

Cheers,                Andrew.
-- 
_____________________________________________________________________          Andrew McMillan, e-mail:
Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709