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 7EA7B565F9 for ; Thu, 17 Mar 2005 09:51:59 +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 64364-09 for ; Thu, 17 Mar 2005 09:51:56 +0000 (GMT) Received: from smtpq2.home.nl (smtpq2.home.nl [213.51.128.197]) by svr1.postgresql.org (Postfix) with ESMTP id EB4AA56627 for ; Thu, 17 Mar 2005 09:51:53 +0000 (GMT) Received: from [213.51.128.134] (port=60969 helo=smtp3.home.nl) by smtpq2.home.nl with esmtp (Exim 4.30) id 1DBrfX-00021V-1r; Thu, 17 Mar 2005 10:51:55 +0100 Received: from cc756500-a.ensch1.ov.home.nl ([82.75.198.118]:37173 helo=[192.168.0.135]) by smtp3.home.nl with esmtp (Exim 4.30) id 1DBrfW-0004OA-0v; Thu, 17 Mar 2005 10:51:54 +0100 Message-ID: <42395339.1080708@magproductions.nl> Date: Thu, 17 Mar 2005 10:51:53 +0100 From: Alban Hertroys User-Agent: Mozilla Thunderbird 0.9 (X11/20041124) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Heather Johnson 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> In-Reply-To: <42388A58.6020604@nypost.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AtHome-MailScanner-Information: Neem contact op met support@home.nl voor meer informatie X-AtHome-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/786 X-Sequence-Number: 75101 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, -- Alban Hertroys MAG Productions