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(at)catalyst(dot)net(dot)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