Multi-table insert using RULE - how to handle id?
От | Collin Peters |
---|---|
Тема | Multi-table insert using RULE - how to handle id? |
Дата | |
Msg-id | df01c91b0607191042r15d40c9fkd4ac6f3cef2ac9d3@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Multi-table insert using RULE - how to handle id?
|
Список | 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 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
В списке pgsql-sql по дате отправления: