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
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 |