X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 445DF55683 for ; Wed, 16 Mar 2005 19:35:06 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76146-06 for ; Wed, 16 Mar 2005 19:34:54 +0000 (GMT) Received: from thea.sephie.org (h-67-100-183-100.nycmny83.covad.net [67.100.183.100]) by svr1.postgresql.org (Postfix) with ESMTP id 1255256437 for ; Wed, 16 Mar 2005 19:34:51 +0000 (GMT) Received: from [192.168.1.152] (unknown [192.168.1.152]) by thea.sephie.org (Postfix) with ESMTP id 4433A7B0783 for ; Wed, 16 Mar 2005 14:30:41 -0500 (EST) Message-ID: <42388A58.6020604@nypost.com> Date: Wed, 16 Mar 2005 14:34:48 -0500 From: Heather Johnson User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: help with plpgsql function called by trigger References: <421B8D2D.6050800@nypost.com> <20050222200915.GA71591@winnie.fuhr.org> <421B9674.4060807@nypost.com> <1109105459.30529.119.camel@state.g2switchworks.com> <421B9E75.9050304@nypost.com> In-Reply-To: <421B9E75.9050304@nypost.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.331 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/760 X-Sequence-Number: 75075 Hello-- I need to make sure that every time a row is inserted into a table called "users" rows are automatically inserted into two other tables: "join_bd" and "behavior_demographics". The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called "users_id"). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Now I want to trigger this function whenever there is an insert on the users table, so I did this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(); The problem is that I need to be able to send the value of the users_id that was generated by the insert into users to insert_bd_join_bd(). The users id value is generated by a sequence. So I think I'd need something like this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(***USERS ID GENERATED BY SEQ FROM LAST USERS INSERT***); I'm new to writing plpgsql and to triggers, so I'm probably missing something, or doing this wrong, but I can't figure out from the docs how to send this value to the function. Can someone help point me to some docs that might help, or tell me why I'm on the wrong track? Thanks so much! Heather Johnson