Re: inserting to a multi-table view

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.
>
> 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