Re: [SQL] Retrieving the new "nextval" for primary keys....

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