DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=SuxCwHEatFK/PkSiHsu2xWcd7kMj8eIZSoe3AeSvaJF7VJw0wSsEAtX/PrcNnz2f/q3aQFYQql3Rc8YdmvLOlMQ8sUaPDj/On297tSbFsSpAu9amGOdze1qQ1bsloznsGSvuQbqJLMP00K88wUKkRAaDSxppczWZ2MszuytSZ2I=
Message-ID: <df01c91b0705031004vbedf4d8h8c76a102617e9ccb@mail.gmail.com>
Date: Thu, 3 May 2007 10:04:26 -0700
From: "Collin Peters" <cadiolis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Insert into VIEW using RULE. Not possible to use nextval()?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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