X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 829B33A1D4E for ; Sat, 15 Jan 2005 08:46:41 +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 90038-10 for ; Sat, 15 Jan 2005 08:46:28 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 083A23A198B for ; Sat, 15 Jan 2005 08:46:29 +0000 (GMT) Received: (qmail 27416 invoked by uid 500); 15 Jan 2005 09:00:23 -0000 Date: Sat, 15 Jan 2005 03:00:22 -0600 From: Bruno Wolff III To: Jarek Pude?ko Cc: pgsql-sql@postgresql.org Subject: Re: return value of the trigger function Message-ID: <20050115090022.GA27243@wolff.to> Mail-Followup-To: Jarek Pude?ko , pgsql-sql@postgresql.org References: <41E28E05.2040307@tech.ceti.pl> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <41E28E05.2040307@tech.ceti.pl> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/109 X-Sequence-Number: 19897 On Mon, Jan 10, 2005 at 15:15:33 +0100, Jarek Pude?ko wrote: > Hi > > I have big problem with a trigger function. > Defs: > > CREATE TABLE foo (id int2, name varchar(20)); > > foo.id cannot be serial or autoint because it will not be unique. > > Now I need a trigger that return foo.id of the inserted record. > > INSERT INTO foo VALUES (max(foo.id)+1,'junk'); If you are really doing the above, you probably do want to use serial. If you insist on not doing this your app should do something like lock table, select max(foo.id)+1 from id, and then nsert using the value returned bye the select and then whatever else you want to do. > > > IMHO the trigger should be: > > CREATE TRIGGER tr_get_new_id > AFTER INSERT on foo > ON EACH ROW > EXECUTE PROCEDURE get_new_id(); > > But I cannot create the function :( > I don't know what type should be input and how about output? trigger or > int2? > > TIA, > > > -- > Jarek Pudelko > JP272-RIPE > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)