Re: inserting to a multi-table view

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4(at)une(dot)edu(dot)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.
>
> create or replace function newperson (studentinfo) returns setof person as
> $$
> declare
> arec person%rowtype;
> begin
> for arec in
> insert into person (foo,bar) values ($1.foo,$1.bar) returning *
> loop
> -- insert into address (...) values (arec.person_id, $1....)
> -- insert into phone (...) values (arec.person_id, $1....)
> return next arec;
> end loop;
> return;
> end;
> $$
> language plpgsql volatile;
> create rule atest as on insert to studentinfo do instead (
> insert into student (person_id) select (select person_id from
> newperson(new));
> );

Here is another question: why does "newperson" have to be a table
function (returning SETOF)? It seems to work fine for me to do

create or replace function newperson (studentinfo) returns integer as $$
declare
pid integer;
begin
insert into person (foo,bar) values ($1.foo,$1.bar) returning
person_id into pid;
return pid;
end; $$ language plpgsql;

create rule atest as on insert to studentinfo do instead
insert into student (person_id, baz) values (newperson(new), new.baz);

Mike