DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=hAwewFlBowxY5Q9xUfjq1wxD1o4ZaUQ7xhCuKvnZdL4a3lp/jqRmsyMucKdX7XeaMfzDj03ucCSvIc5jTHjz8MiANCsjrS7DCFN173P2hzPk6vYaOrFk1YgFWlY8YxKLt0Yvud9yJGiFUscILtD1OVSfML/dRRX9jMZNXf53NMc=
Message-ID: <df01c91b0607191042r15d40c9fkd4ac6f3cef2ac9d3@mail.gmail.com>
Date: Wed, 19 Jul 2006 10:42:56 -0700
From: "Collin Peters" <cadiolis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Multi-table insert using RULE - how to handle id?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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