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

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
==================================================================