How to get inserted id from postgres

cormanaz has asked for the wisdom of the Perl Monks concerning the following question:

I have a subroutine I use to insert records into a postgres db. It takes as arguments the db handle, table name, and a hash containing the record to insert. I am trying to modify this to return the id of the record just inserted:

sub insertsql {
    my ($dbh,$table,$data) = @_;
    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) 
+returning id";
    my $sth = $dbh->prepare($sqlstatement);
    my $id = $sth->execute(@values) || die "Could not execute statemen
+t: $sqlstatement $sth->errstr";
    $sth->finish();
    return $id;
}
[download]

According to postgres docs, including "returning id" in the query should make it return the last id inserted, but it doesn't. If I run this three times I get a "1" returned on each call even though when I look at the DB table I get three records with ids 1,2,3. Anybody know What I am doing wrong?

Comment on How to get inserted id from postgres Download Code

Replies are listed 'Best First'.
Re: How to get inserted id from postgres by Corion (Patriarch) on Sep 30, 2022 at 20:53 UTC
The `returning` clause basically turns your `INSERT` statement (also) into a `SELECT` statement, so after `->execute()`, you want to `->fetch` the result: `... my $sqlstatement = "insert into $table ($keylist) values ($qlist) +returning id"; my $sth = $dbh->prepare($sqlstatement); $sth->execute(@values) \|\| die "Could not execute statement: $sqlst +atement $sth->errstr"; my $id = $sth->fetchall_arrayref->[0]->[0]; ...` [download]	[reply] [d/l] [select]
Re: How to get inserted id from postgres by AnomalousMonk (Archbishop) on Sep 30, 2022 at 22:54 UTC
`$sth->execute(...) \|\| die "...: $sqlstatement $sth->errstr";` Tangentially, note that the object reference `$sth` in the double-quoted string in the statement quoted above will stringify to something like `HASH(0x123abc)` and the printed output will look something like `"... HASH(0x123abc)->errstr"` (update: unless maybe it's tie-ed or something weird like that). See Perl::Critic policy to catch quoted execution? An effective alternative would be (untested): `$sth->execute(...) \|\| die "...: $sqlstatement ", $sth->errstr;` Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re: How to get inserted id from postgres by dsheroh (Monsignor) on Oct 01, 2022 at 12:51 UTC
I haven't worked with postgres databases in over a decade, so I can't say with certainty how well DBD::Pg supports it, but my first thought would be to try `$dbh->last_insert_id` as a (more-or-less database-independent) alternative to trying to get the statement to return the ID when executed. The reason you always get a result of 1 in `$id` is because, per the DBI docs, "For a non-SELECT statement, execute returns the number of rows affected, if known." INSERTing one row = 1 row affected.	[reply] [d/l] [select]