Re: inserting to a multi-table view

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Michael Shulman" <shulman(at)mathcamp(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: inserting to a multi-table view
Date: 2008-06-17 03:03:40
Message-ID: dcc563d10806162003t457bc7f7tea1c28b288ba7dc5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Jun 16, 2008 at 8:49 PM, Michael Shulman <shulman(at)mathcamp(dot)org> 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?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2008-06-17 03:27:16 Re: inserting to a multi-table view
Previous Message Michael Shulman 2008-06-17 02:49:12 inserting to a multi-table view