Sunday, May 13, 2012

Using insert .. returning with perl and PostgreSQL

PostgreSQL provides the ability to return a value on insert. so for example:

create table foo (id serial, name text);
insert into foo('dave') returning id;
Will return the auto-generated value for "id" assigned to 'dave'
You can leverage this from within Perl by doing something like this:
my $foo_insert = "insert into foo(?) returning id"
my $foo_cur = $dbh->prepare($foo_insert);
$foo_cur->execute('Dave');
my $foo_rec = $foo_cur->fetchrow_hashref();
print $foo_rec{"id"};

2 comments:

  1. I think the correct is print $foo_rec->{"id"}; - at least in my machine it is.

    ReplyDelete
  2. That's entirely possible! This post is really olde and I don't use perl much anymore. I'm guessing either a typo or perl changed. Thanks for the correction!

    ReplyDelete

Web Statistics