Insert into VIEW using RULE. Not possible to use nextval()?
От | Collin Peters |
---|---|
Тема | Insert into VIEW using RULE. Not possible to use nextval()? |
Дата | |
Msg-id | df01c91b0705031004vbedf4d8h8c76a102617e9ccb@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Insert into VIEW using RULE. Not possible to use nextval()?
|
Список | pgsql-sql |
I am having the same problem that is documented elsewhere in the archives. Namely when you have a INSERT RULE on a VIEW the nextval() function doesn't behave properly (or it doesn't behave how I'd like it to). http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php I'm wondering if anything has changed for this in regards to best practices? Suggested solutions are to change to a trigger or use currval() for your secondary INSERTS inside the RULE. A trigger does not apply to my case as I am basically using this as a shortcut to manually doing two INSERTs. Is there any problems with using the currval() approach? If I use that within the same call is there any chance of that not returning the correct value? (e.g. if this INSERT RULE is being called a 1000 times at once, is it guaranteed to be correct? Another option I see is to place the INSERT inside a LOOP. For example instead of: INSERT INTO user_activity_single(user_activity_id, activity_date, user_activity_type_id, user_activity_action_id, user_id, div1) SELECT nextval('user_activity_user_activity_id_seq'), etc.... have: FOR mviews IN SELECT nextval('user_activity_user_activity_id_seq') as id, CURRENT_DATE, 1, 2, 27, 'foo' LOOP INSERT INTO user_activity_single(mviews.id, etc...) END LOOP; Performance wise this doesn't seem as good. In my case the SELECT statement would be around 4000 records. Any tips for me? Regards, Collin Peters
В списке pgsql-sql по дате отправления: