From: | "Collin Peters" <cadiolis(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Insert into VIEW using RULE. Not possible to use nextval()? |
Date: | 2007-05-03 17:04:26 |
Message-ID: | df01c91b0705031004vbedf4d8h8c76a102617e9ccb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-05-03 17:26:59 | Re: Insert into VIEW using RULE. Not possible to use nextval()? |
Previous Message | Bart Degryse | 2007-05-03 14:20:51 | Re: Needs Function |