Getting id of postgres record inserted via DBI

[cormanaz]

Good day Monks. I have been using a little subroutine to do inserts on MySQL (via DBI and Pg) and get the inserted id:

sub insertsql {
    my ($dbh,$table,$data,$ignore) = @_;
    my @qm;
    my @keys;
    my @values;
    my $i = -1;
    foreach my $k (keys %$data) {
        if (defined($data->{$k})) {
            $i++;
            $keys[$i] = $k;
            $values[$i] = $data->{$k};
            $qm[$i] = '?';
        }
    }
    my $keylist = join(",",@keys);
    my $qlist = join(",",@qm);
    my $sqlstatement = "insert into $table ($keylist) values ($qlist)"
+;
    if ($ignore) {
        my $sqlstatement = "insert ignore into $table ($keylist) value
+s ($qlist)";
    }
    
    my $sth = $dbh->prepare($sqlstatement);
    #$sth->execute(@values) || die "putsql could not execute MySQL sta
+tement: $sqlstatement $sth->errstr";
    $sth->execute(@values) || die $sth->errstr;
    $sth->finish();
    return $dbh->{'mysql_insertid'};
}

I'm now working with Postgres and, alas, there is no 'mysql_insertid' for postgres, and it doesn't provide such an easy way to get the inserted id.

I found this thread on Stack Overflow where somebody suggests using a separate statement SELECT currval(pg_get_serial_sequence('[tablename]','id')) but that returns nothing. They also suggest adding returning id to the end of the insert statement. But I can't figure out how to make that work in the context of the subroutine.

Can anyone help?

[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

[soonix]

I know Postgres only from hearsay, but this other SO answer says you can have the INSERT statement RETURNING the inserted id, and execute in DBD::Pg shows an example.

[ikegami]

I'm now working with Postgres and, alas, there is no 'mysql_insertid' for postgres

Doesn't the generic DBI $dbh->last_insert_id work?