On Mon, Jun 16, 2008 at 8:49 PM, Michael Shulman <[EMAIL PROTECTED]> 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'.
Got a short example of what you've tried so far? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
