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
