X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E3B069FA4AA for ; Wed, 19 Jul 2006 22:34:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 14558-10 for ; Wed, 19 Jul 2006 22:34:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id DED779FA4A7 for ; Wed, 19 Jul 2006 22:34:12 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so621751uge for ; Wed, 19 Jul 2006 18:34:10 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=Go9d9g3vdiYMWIeo1hU9EvVXHc2O2r+gpqMdWx/Zpm+/IRJVZYiWMWN2jXLr0goe6bjzfsE2NfomfvRXGLOD6kXf5Iwlha16c8m41TOhDR2gfve6r8GhSEky83V68x614e0g/SV/RkbZq4smY/oPy9UXFNm6moA4X4OiOgPepfE= Received: by 10.78.195.9 with SMTP id s9mr54212huf; Wed, 19 Jul 2006 18:34:10 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Wed, 19 Jul 2006 18:34:10 -0700 (PDT) Message-ID: Date: Wed, 19 Jul 2006 20:34:10 -0500 From: "Aaron Bono" To: "Collin Peters" Subject: Re: Multi-table insert using RULE - how to handle id? Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_55585_28745330.1153359250818" References: X-Google-Sender-Auth: 1e1057dd570b2442 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/257 X-Sequence-Number: 25831 ------=_Part_55585_28745330.1153359250818 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/19/06, Collin Peters 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 ================================================================== ------=_Part_55585_28745330.1153359250818 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_55585_28745330.1153359250818--