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 3714356457 for ; Thu, 17 Mar 2005 03:48:32 +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 59713-02 for ; Thu, 17 Mar 2005 03:48:22 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 3A2575642A for ; Thu, 17 Mar 2005 03:48:21 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j2H3mJIp023583 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 16 Mar 2005 20:48:23 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j2H3mJQu023476; Wed, 16 Mar 2005 20:48:19 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j2H3mGTk023455; Wed, 16 Mar 2005 20:48:16 -0700 (MST) (envelope-from mfuhr) Date: Wed, 16 Mar 2005 20:48:16 -0700 From: Michael Fuhr To: Heather Johnson Cc: pgsql-general@postgresql.org Subject: Re: help with plpgsql function called by trigger Message-ID: <20050317034816.GA13674@winnie.fuhr.org> 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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42388A58.6020604@nypost.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/776 X-Sequence-Number: 75091 On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote: > CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' What version of PostgreSQL are you using? The "opaque" type has been deprecated since 7.3; recent versions should use "trigger". And trigger functions aren't declared with arguments -- if the function needs arguments, then use TG_ARGV. But I think your code can use NEW instead of a function argument. > 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'; Based on the rest of your description, I think you're looking for something like this: CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS ' DECLARE bdid integer; BEGIN INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid); bdid := currval(''behavioral_demographics_bdid_seq''); INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid); RETURN NULL; END; ' LANGUAGE plpgsql VOLATILE; See the "Trigger Procedures" section of the PL/pgSQL chapter in the documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and see the "Sequence Manipulation Functions" section of the "Functions and Operators" chapter to learn more about currval(). -- Michael Fuhr http://www.fuhr.org/~mfuhr/