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 7A8A25643B for ; Thu, 17 Mar 2005 16:08:44 +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 87780-02 for ; Thu, 17 Mar 2005 16:08:39 +0000 (GMT) Received: from nypost.com (ms1.nypost.com [206.15.109.53]) by svr1.postgresql.org (Postfix) with ESMTP id 0BB5F56400 for ; Thu, 17 Mar 2005 16:08:37 +0000 (GMT) Received: from ([10.238.89.21]) by ms1.nypost.com with ESMTP id 4220067.4777238; Thu, 17 Mar 2005 11:07:22 -0500 Received: from [10.238.16.155] ([10.238.16.155]) by NYPEXCH01.NYPOST with Microsoft SMTPSVC(5.0.2195.6713); Thu, 17 Mar 2005 11:07:22 -0500 Message-ID: <4239AB06.801@nypost.com> Date: Thu, 17 Mar 2005 11:06:30 -0500 From: Heather Johnson User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alban Hertroys Cc: pgsql-general@postgresql.org Subject: Re: 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> <42388A58.6020604@nypost.com> <42395339.1080708@magproductions.nl> In-Reply-To: <42395339.1080708@magproductions.nl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 17 Mar 2005 16:07:22.0832 (UTC) FILETIME=[67570D00:01C52B0B] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.234 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200503/800 X-Sequence-Number: 75115 Thank you! That *is* nicer. And thanks to Michael Fuhr too for his reference to the appropriate docs and suggestions. Between your two responses, I have a much better sense of how to go about this and where to look for extra help. Heather Alban Hertroys wrote: > Heather Johnson wrote: > >> 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'; > > > Wouldn't it be nicer to put the second part of that SP in a trigger on > behavioral_demographics? That can only work if inserting into users is > the only way to insert records into behavioral_demographics, of course. > > I don't know the names of your columns in behavioral_demographics, but > it would look something like this: > > CREATE FUNCTION insert_bd () RETURNS trigger AS ' > begin > INSERT INTO behavioral_demographics (users_id) > VALUES (NEW.users_id); -- fires trigger on bd > end; > ' LANGUAGE 'plpgsql'; > > CREATE FUNCTION insert_join_bd () RETURNS trigger AS ' > begin > INSERT INTO join_bd (bd_id, users_id) > VALUES (NEW.bd_id, NEW.users_id); > end; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER insert_bd_on_users > AFTER INSERT ON users > FOR EACH ROW EXECUTE PROCEDURE insert_bd(); > > CREATE TRIGGER insert_join_bd_on_users > AFTER INSERT ON behavioral_demographics > FOR EACH ROW EXECUTE PROCEDURE insert_join_bd(); > > > Considering these SP's are basically simple SQL statements, the triggers > could also be implemented as a set of query rewrite rules (see CREATE > RULE). That's usually more efficient, but I don't have a lot of > experience with those... > > Regards, >