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.
Regards,
Collin
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
