Re: inserting to a multi-table view

Michael Shulman wrote:
> On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4@une.edu.au> wrote:
>
>> The only way I could find to make this work is to use a rule and wrap the
>> inner "insert returning" in a function.
>>
>
> Thanks, this works!  Although it feels like something of a hack;
> shouldn't there be a more elegant solution?
>
The more elegant statements don't work.

The ideal solution would be if some variant of
  insert into student (person_id) values ((insert into person (...)
values (...) returning person_id));
worked.


> Also, I don't understand why
>
>
>> create rule atest as on insert to studentinfo do instead (
>>  insert into student (person_id) select (select person_id from newperson(new));
>> );
>>
>
> is necessary; what is wrong with
>
> create rule atest as on insert to studentinfo do instead (
>  insert into student (person_id) select person_id from newperson(new);
> );
>
> ?  (Other than the evident fact that it doesn't work; the error
> message "function expression in FROM cannot refer to other relations
> of same query level" is not illuminating to me.)
>
Got it in 1 - it doesn't work.  I'm sure there's a good reason for the
error message that someone who knows more about rules can explain.


> Additionally, is there a reason why you put one of the inserts in the
> function and the other in the rule?  Why not have the function do both
> inserts and then the rule just invoke the function?
>
>
If the rule does the insert into student, then the return to the
application looks like a normal insert (e.g. you can check rows affected).

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au