Re: inserting to a multi-table view

Michael Shulman wrote:
> On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)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(at)une(dot)edu(dot)au