Received: from localhost.localdomain (h246.ozemail2.ozemail.com.au [203.108.14.246]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA02961 for ; Mon, 12 Jul 1999 23:13:11 -0400 (EDT) (envelope-from chris@tech.com.au) Received: from tech.com.au (localhost [127.0.0.1]) by localhost.localdomain (8.9.3/8.9.3) with ESMTP id NAA12452; Tue, 13 Jul 1999 13:12:49 +1000 Sender: chris@localhost.localdomain Message-ID: <378AAEB1.D3EC3F0F@tech.com.au> Date: Tue, 13 Jul 1999 13:12:49 +1000 From: Chris Bitmead X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.6 i686) X-Accept-Language: en MIME-Version: 1.0 To: Heinz Hemken CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] variables in pgSQL? References: <378AA772.84903F66@franchiseloan.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit CREATE SEQUENCE id_seq; INSERT INTO masterindex_table(user_id, name) SELECT nextval('id_seq'), 'Mr Foo' from masterindex_table where 12345 not in (SELECT user_id from masterindex_table); This will create a new record in masterindex_table only if the id 12345 is not already existing. All in one SQL command! Heinz Hemken wrote: > > The following is legal in MS SQL: > > declare @user_id int > select @user_id = user_id from masterindex_table where masterindexid = 1 > > whereupon the local variable can be used for other things. Is there > anything similar in pgSQL? I want to have a user table for a web > database, and I want to be able to do a begin/commit block where I 1) > check to see if the user exists in an account table, 2) if not, get the > next available userid from a master index table, 3) add a new userid to > the account table for the new user, and 4) increment the userid field in > the master index table. I want this to occur all within a single > transaction block so that no race conditions exist, and the masterindex > table is only being used by one process at a time for the whole > operation. > > How can I do this in postgresql? Is there a better approach? > > -- > Heinz Hemken > IFC Capital Corporation > San Diego, C