Re: inserting to a multi-table view

Michael Shulman wrote:
> On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>>> I can write a trigger
>>> function that does the right thing, with 'INSERT ... RETURNING
>>> person_id INTO ...', but Postgres will not let me add an INSERT
>>> trigger to a view; it says 'ERROR: "studentinfo" is not a table'.
>>>
>> Got a short example of what you've tried so far?
>>
>
> create function ins_st() returns trigger as $$
> declare
>   id integer;
> begin
>   insert into person (...) values (NEW....) returning person_id into id;
>   insert into student (person_id, ...) values (id, NEW....);
> end;
> $$ language plpgsql;
>
> create trigger ins_student before insert on studentinfo
>   for each row execute procedure ins_st();
>
> ERROR:  "studentinfo" is not a table
>
> Mike
>
>

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));
);


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