Skip site navigation (1) Skip section navigation (2)

Re: Multi-table insert using RULE - how to handle id?

From: "Aaron Bono" <postgresql(at)aranya(dot)com>
To: "Collin Peters" <cadiolis(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Multi-table insert using RULE - how to handle id?
Date: 2006-07-20 01:34:10
Message-ID: bf05e51c0607191834n4bb56079oc2a1cc2cbb84adfa@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On 7/19/06, Collin Peters <cadiolis(at)gmail(dot)com> wrote:
>
> I am learning about how to use rules to handle a multi-table insert.
> Right now I have a user_activity table which tracks history and a
> user_activity_users table which tracks what users are associated with
> a row in user_activity (one to many relationship).
>
> I created a rule (and a view called user_activity_single) which is to
> simplify the case of inserting a row in user_activity in which there
> is only one user in user_activity_users.
>
> CREATE OR REPLACE RULE user_activity_single_insert AS
> ON INSERT TO user_activity_single
> DO INSTEAD (
>         INSERT INTO user_activity(
>                 user_activity_id,
>                 description,
>                 ...
>         )
>         VALUES (
>                 NEW.user_activity_id,
>                 NEW.description,
>                 ...
>         );
>         INSERT INTO user_activity_users (
>                 user_activity_id,
>                 user_id
>         )
>         VALUES (
>                 NEW.user_activity_id,
>                 NEW.user_id
>         );
> );
>
> This works well by itself, but the problem is that I have to manually
> pass in the user_activity_id which is the primary key.  I do this by
> calling nextval to get the next ID in the sequence.
>
> Is there any way to have the rule handle the primary key so I don't
> have to pass it in?  It seems you can't use pgsql inside the rule at
> all.  What I'm looking for is something like:
>
> CREATE OR REPLACE RULE user_activity_single_insert AS
> ON INSERT TO user_activity_single
> DO INSTEAD (
>         SELECT nextval('user_activity_user_activity_id_seq') INTO next_id;
>
>         INSERT INTO user_activity(
>                 user_activity_id,
>                 description,
>                 ...
>         )
>         VALUES (
>                 next_id,
>                 NEW.description,
>                 ...
>         );
>         INSERT INTO user_activity_users (
>                 user_activity_id,
>                 user_id
>         )
>         VALUES (
>                 next_id,
>                 NEW.user_id
>         );
> );
> Note the sequence stored in next_id.  This doesn't work as it
> complains about next_id in the INSERT statements.  Any way to do
> something like this?  I suppose I could create a function and then
> have the rule call the function but this seems like overkill.



Since I have not tried something like this before, I may be off base but
have you tried:

CREATE OR REPLACE RULE user_activity_single_insert AS
 ON INSERT TO user_activity_single
DO INSTEAD (
       INSERT INTO user_activity(
               description,
               ...
       )
       VALUES (
               NEW.description,
               ...
       );
       INSERT INTO user_activity_users (
               user_activity_id,
               user_id
       )
       VALUES (
               SELECT currval('user_activity_user_activity_id_seq'),
               NEW.user_id
       );
);

I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

In response to

Responses

pgsql-sql by date

Next:From: Ross JohnsonDate: 2006-07-20 02:37:14
Subject: Re: Multi-table insert using RULE - how to handle id?
Previous:From: Phillip SmithDate: 2006-07-19 23:51:53
Subject: FW: Table Join (Maybe?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group