Michael Shulman wrote:
On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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@xxxxxxxxxx
