Michael Shulman wrote:
On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4@xxxxxxxxxx> 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@xxxxxxxxxx
