Re: Insert..returning (was Re: Re: postgres TODO)

At 12:15 12/07/00 +1000, Philip Warner wrote:
>
>The only commercial DB that implements this kind of behaviour does it on
>update only, and restricts it to updates that only affect one row. As a
>first pass, it would satisfy 99.9% of users needs to only allow this
>feature on inserts & updates that affected one row.
>

The more I think about this, the more I think they probably had a good
reason for doing it. The cleanest solution seems to be that updates &
inserts affecting more than one row should produce an error.

I'd be very interested in how people think rules and triggers should be
handled.

My initial inclination is that if a trigger prevents the insert, then it is
the responsibility of the programmer to check the number of rows affected
after the update (the returned fields would either not exist, or be null).

If a rule rewrites the insert as an insert into another table, then I am
not sure what is best: either raise an error, or return the fields from the
*real* target table. I *think* I prefer raising an error, since any other
behaviour could be very confusing.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 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   |/