Message-ID: <3D6CF5B7.90206@nurseamerica.net>
Date: Wed, 28 Aug 2002 11:09:27 -0500
From: Kevin Brannen <kevinb@nurseamerica.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Greg Patnude <GPatnude@adelphia.net>
Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [SQL] Retrieving the new "nextval" for primary keys....
References: <ak6lse$a9a$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Greg Patnude wrote:
> I am using postgreSQL with Perl::CGI and Perl::DBI::Pg... I would like to be
> able to insert a row from my Perl script [$SQL->exec();] and have postgreSQL
> return the id of the newly inserted record (new.id) directly to the Perl
> script for further processing... Anyone with a solution / idea ???
> 
> Nearly EVERY table I create in postgreSQL (7.2) has the following minimum
> structure:
> 
> create table "tblName" (
> 
>     id int4 primary key nextval ("tblName_id_seq"),
> 
>     ..field...
> )

You can either do it in 2 statements, something like:

$dbh->do("insert into tblName ...");
my ($id) = $dbh->selectrow_array("select currval('tblName_id_seq')");

Or you could create a function which takes the insert statement, and 
ends with doing a select on the currval (as above) and returning that. 
As I do the 2 statement approach above, I haven't done a function, but 
it doesn't look like it would be that hard to do.

HTH,
Kevin