Re: Multi-table insert using RULE - how to handle id? - Mailing list pgsql-sql

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.