Re: Odd Class::DBI sequence behaviour

it's not odd at all, it's doing exactly what your telling it to do. solution is to either not try to set the value your self and just let CDBI do the insert, or if you must absolutely know the value of the id before you insert, change the table id column to remove the default value, and set up your own sequence. Now you can ask for the nextval, incrementing the sequence +1, then do your insert without Pg re-incrementing it.

package MyClass::DBI::SubClass;
use base MyClass::DBI;

__PACKAGE__->table('my_items');
__PACKAGE__->columns(All=>qw/id name qty parent/);

sub next_val {
   my $self = shift;
   my $sth = $self->db_Main->prepare(
      qq(SELECT nextval(\'my_items_id_seq\') ) );
      $sth->execute();
   my $id = $sth->fetch();
   return $id;
};

sub curr_value {
  my $self = shift;
  my $sth = $self->db_Main->prepare(
         qq(SELECT currval(\'my_items_id_seq') ));
     $sth->execute();
  my $id = $sth->fetch();
  return $id;
};
[download]

Meanwhile, in a nearby piece of code.

package MyClass::Items;
use 5.008008;
use strict;
use warnings;

use MyClass::DBI::SubClass;

sub addItem {
   my $self = shift;
   my %params = @_;
   return undef if(! defined($params{name}));
   return undef if(! defined($params{qty}));

   my $record = MyClass::DBI::SubClass->insert(
      {
          id => MyClass::DBI::SubClass->next_val(),
          name => $params{name},
          qty => $params{qty}
      });
   if(!defined($record)) {
      warn "Unable to create new item: ". $params{name};
      return undef;
   };
   return $record;
};

1;
__END__
[download]

And that "Should" do what you want. Though I still can't imagine why you would want to know the id of the item before insert. Even if you're linking a different record to it, as soon as you do the insert, the CDBI object knows the id. So...

my $parent = MyClass::DBI::SubClass->insert( {name=>"dad", qty=>1} );
my $child = MyClass::DBI::SubClass->insert(
   {
        name => "junior",
        qty => 1,
        parent => $parent->id
   });
[download]

even though you never looked up the item again, CDBI just knows. thus it's automagic goodness.

Comment on Re: Odd Class::DBI sequence behaviour Select or Download Code