Re: Getting id of postgres record inserted via DBI

[cavac]

Modern PostgreSQL supports the RETURNING keyword. Basically, you treat the statement as a one line SELECT afterwards.

my $sth = $dbh->prepare_cached("INSERT INTO bla (colname1, colname2) V
+ALUES (?, ?) RETURNING bla_id")
    or croak($dbh->errstr);
$sth->execute($val1, $val2) or croak($dbh->errstr);
my $line = $sth->fetchrow_hashref;
$sth->finish;
$dbh->commit;

print "Inserted data with new primary key ", $line->{bla_id}, "\n";

See also: https://www.postgresql.org/docs/current/dml-returning.html

[erix]

Modern PostgreSQL supports the RETURNING keyword.

s/Modern /Since time immemorial /;

because INSERT ... RETURNING * is in PostgreSQL since 2006, version 8.2.

[marto]

For some people, 2006 tech is futuristic :P

[cavac]

I agree that i'm old, yes. I started with PostgreSQL 7 or something like that (after having to work with Oracle for many years).