Re: inserting to a multi-table view

Michael,

You can try the following:

CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
DO INSTEAD
(
INSERT INTO person ...;
INSERT INTO student(person_id,...) VALUES
(currval('person_person_id_seq'),...);
);

The currval() function gives you the value of the sequence associated to
your field. The name of the sequence should be the one in my example.

Philippe Gregoire
Information Manager
www.boreal-is.com

Michael Shulman wrote:
> Hi,
>
> This feels like a very basic question but I cannot figure it out.
> Suppose I have two tables and a view that combines their data:
>
> CREATE TABLE person
> (person_id SERIAL PRIMARY KEY,
> ...);
>
> CREATE TABLE student
> (student_id SERIAL PRIMARY KEY,
> person_id INTEGER REFERENCES person,
> ...)
>
> CREATE VIEW studentinfo AS
> SELECT * FROM person JOIN student USING person_id;
>
> I want to be able to do INSERTs on "studentinfo" and have rows created
> in both "person" and "student". This requires first inserting into
> "person", capturing the "person_id" of the resulting row, and using it
> to insert into "student". This seems as though it must be a common
> situation.
>
> I am happy to use either rules or triggers, but I can't figure
> out how to do it with either. I can write a rule that does two
> INSERTs but I don't know how to capture the id resulting from the
> first insert and put it into the second. 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'.
>
> The Postgres manual:
> http://www.postgresql.org/docs/8.3/static/rules-triggers.html
> says "a trigger that is fired on INSERT on a view can do the same as
> a rule: put the data somewhere else and suppress the insert in the
> view." So what do I need to do to make an INSERT trigger on a view?
>
> Thanks,
> Mike
>
>