From: | "Michael Shulman" <shulman(at)mathcamp(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | inserting to a multi-table view |
Date: | 2008-06-17 02:49:12 |
Message-ID: | c3f821000806161949s50596b41la3e20f7788f8fdfa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-06-17 03:03:40 | Re: inserting to a multi-table view |
Previous Message | TJ O'Donnell | 2008-06-17 00:24:49 | cool code_swarm animation of PostgreSQL development since 1996 |