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 ASON 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 ASON 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,
...);INSERTINTO 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.
Regards,
Collin