SPOPS::DBI::Pg -- PostgreSQL-specific routines for the SPOPS::DBI


 # In your configuration:

 'myspops' => {
     'isa' => [ qw/ SPOPS::DBI::Pg SPOPS::DBI / ],

     # If you have a SERIAL field, just set increment_field to a true
     # value

     'increment_field' => 1,

     # If you want to specify the name of your sequence (whether using
     # a SERIAL field or not):

     'sequence_name'   => 'myseq',


This just implements some Postgres-specific routines so we can abstract them out.

One of them optionally returns the sequence value of the just-inserted id field. Of course, this only works if you have a the field marked as 'SERIAL' or using a sequence value in your table:

 CREATE TABLE my_table (
   id  SERIAL,


 CREATE TABLE my_table (
   id int not null primary key,
 CREATE SEQUENCE myobject_sequence;

You must to let this module know if you are using this option by setting in your class configuration the key 'increment_field' to a true value:

 $spops = {
    myobj => {
       class => 'My::Object',
       isa   => [ qw/ SPOPS::DBI::Pg  SPOPS::DBI / ],
       increment_field => 1,

If you use the 'SERIAL' datatype then you do not have to specify a sequence name. Otherwise you need to tell SPOPS what sequence to use in the class configuration:

 $spops = {
    myobj => {
       class           => 'My::Object',
       isa             => [ qw/ SPOPS::DBI::Pg  SPOPS::DBI / ],
       increment_field => 1,
       sequence_name   => 'myobject_sequence',

NOTE: The name automatically created by PostgreSQL when you use the 'SERIAL' datatype follows a certain convention ($table-$idfield-seq). But if the table or ID field are too long, PostgreSQL will truncate the name so it will fit in the 32-character limit for symbols. In this case you will either need to recompile PostgreSQL (yuck) or list the sequence name in the class configuration. See a message from the openinteract-help mailing list at:

for more information on recompiling if you are so inclined.



Returns 'CURRENT_TIMESTAMP()', used in PostgreSQL to return the value for right now.

sql_quote( $value, $data_type, [ $db_handle ] )

DBD::Pg depends on the type of a field if you are quoting values to put into a statement, so we override the default 'sql_quote' from SPOPS::SQLInterface to ensure the type of the field is used in the DBI->quote call.

The $data_type should correspond to one of the DBI datatypes (see the file 'dbi_sql.h' in your Perl library tree for more info). If the DBI database handle $db_handle is not passed in, we try to find it with the class method global_datasource_handle().

pre_fetch_id( \%params )

If 'increment_field' is not set we do not fetch an ID. If 'sequence_name' is not also set we do not fetch an ID, assuming that you have defined the ID field using the 'SERIAL' datatype.

Otherwise we go ahead and fetch an ID from the specified sequence.

post_fetch_id( \%params )

If you are using a SERIAL column (indicated by no 'sequence_name') we fetch the value used by the database for this sequence.





Copyright (c) 2001-2004, inc.. All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.


Chris Winters <>