From: Aaron Bono <postgresql@aranya.com>
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multi-table insert using RULE - how to handle id?
Date: Wed, 19 Jul 2006 20:34:10 -0500
Message-ID: <bf05e51c0607191834n4bb56079oc2a1cc2cbb84adfa@mail.gmail.com> (raw)
In-Reply-To: <df01c91b0607191042r15d40c9fkd4ac6f3cef2ac9d3@mail.gmail.com>
References: <df01c91b0607191042r15d40c9fkd4ac6f3cef2ac9d3@mail.gmail.com>
On 7/19/06, Collin Peters <cadiolis@gmail.com> wrote:
>
> 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.
Since I have not tried something like this before, I may be off base but
have you tried:
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
description,
...
)
VALUES (
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
SELECT currval('user_activity_user_activity_id_seq'),
NEW.user_id
);
);
I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, cadiolis@gmail.com
Subject: Re: Multi-table insert using RULE - how to handle id?
In-Reply-To: <bf05e51c0607191834n4bb56079oc2a1cc2cbb84adfa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox