inserting to a multi-table view
От | Michael Shulman |
---|---|
Тема | inserting to a multi-table view |
Дата | |
Msg-id | c3f821000806161949s50596b41la3e20f7788f8fdfa@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: inserting to a multi-table view
Re: inserting to a multi-table view Re: inserting to a multi-table view Re: inserting to a multi-table view |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: