Обсуждение: Incomplete idea about views and INSERT...RETURNING

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

At 18:03 21/07/01 -0400, Tom Lane wrote:
>
>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?
>

I don't see it as a kludge, just a limitation on the first pass. If people
need the feature then they can recode their DO INSTEAD as a trigger (I
think that works...). You probably need to return useful information to the
application to let it know what has happened, however.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

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

Andrew McMillan <andrew@catalyst.net.nz> writes:
> Tom Lane wrote:
>> 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?

Hmm, so we'd allow INSERT RETURNING to be the last statement of an
ON INSERT DO INSTEAD rule, and the RETURNING clause would either be
dropped (if rewriting a plain INSERT) or used to form the outputs
(if rewriting INSERT RETURNING).  Kind of limited maybe, but it would
work for simple cases, which is a lot better than none at all...

The trouble with INSERT RETURNING followed by SELECT is that a rule
has noplace to keep the results: it hasn't got any local variables.
(And I don't think I want to invent such a feature, at least not on
the spur of the moment.)
        regards, tom lane