Multi-table insert using RULE - how to handle id?

From: "Collin Peters" <cadiolis(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Multi-table insert using RULE - how to handle id?
Date: 2006-07-19 17:42:56
Message-ID: df01c91b0607191042r15d40c9fkd4ac6f3cef2ac9d3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Phillip Smith 2006-07-19 23:51:53 FW: Table Join (Maybe?)
Previous Message Richard Broersma Jr 2006-07-19 15:18:27 Re: User Permission