PostgreSQL and Perl

PostgreSQL and Perl

Right after I started coding the new FreshPorts,
I started to write some Perl scripts to load data into tables.  The first problem was
getting the Perl-PostgreSQL interface installed.  I’d forgotten how I’d done this
with mySQL. This is also known as the PostgreSQL DBI or DBD::Pg.

The easy install

The easiest way to get Perl talking to PostgreSQL is to install a port.  I
followed the instructions found in the FreeBSD
handbook
for compiling
ports from the internet
.

cd /usr/ports/databases/p5-DBD-Pg
make install

The hard install

There is another way.  And this is what I did. I now regret it.  I should
have used the port above.  Unfortunately, I didn’t look for one first.  I was
told about it after I wrote this article.  I suggest you use the port and don’t do
the following.

Don’t use the instructions in the rest of this section.
  Use the port.

The solution is at the PostgreSQL Guide
webpage.  At that site, you’ll find information about the Perl 5 interface for
PostgreSQL
, DBD::Pg.   That’s where I found the link to:

http://www.perl.com/CPAN-local/modules/by-module/DBD/

I suggest you go to the above location and search for DBD-Pg-xxx.tar.gz file which is
the latest and greatest.  At the time of writing, these were the links for me:

http://www.perl.com/CPAN-local/modules/by-module/DBD/DBD-Pg-0.95.readme
http://www.perl.com/CPAN-local/modules/by-module/DBD/DBD-Pg-0.95.tar.gz

Download those files (I suggest you do it as non-root).  Untar
the tar.gz file and have a look at the README file where you’ll find the installation
instructions. 

  1. perl Makefile.PL
  2. make
  3. make test
  4. make install

The instructions suggest that you do steps "1. to 3. as normal user, not as root
!".

You will also have to set the following environment variables.  This is bash
format:


POSTGRES_INCLUDE=/usr/local/pgsql/include; export POSTGRES_INCLUDE
POSTGRES_LIB=/usr/local/pgsql/lib; export POSTGRES_LIB

Here’s what to expect during step 4


# make install Installing
/usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBD/Pg/Pg.so Installing
/usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBD/Pg/Pg.bs Files found in
blib/arch --> Installing files in blib/lib into architecture dependend library tree!
Installing /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/DBD/Pg.pm Installing
/usr/local/lib/perl5/site_perl/5.005/i386-freebsd/DBD/dbd-pg.pod Installing
/usr/local/lib/perl5/5.00503/man/man3/DBD::Pg.3 Installing
/usr/local/lib/perl5/5.00503/man/man3/DBD::dbd-pg.3 Writing
/usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBD/Pg/.packlist Appending
installation info to /usr/libdata/perl/5.00503/mach/perllocal.pod

Sample perl script

Here’s a simple perl script which should get you connected.

#!/usr/bin/perl
#
#
# a simple test script for connecting to the default
# PostgreSQL database.
#
# Copyright 2000 DVL Software Limited
#
# see http://freebsddiary.org/postgresql-perl.html
#

use DBI;
use strict;

my $dbh;
my $sth;
my @vetor;
my $field;

$dbh = DBI->connect('DBI:Pg:dbname=YourDBName', 'UserID', '');
if ($dbh) {
   print "connected\n";

   $sth = $dbh->prepare("SELECT * from table limit 20");
   $sth->execute;

   print "<table>\n";
   while (@vetor = $sth->fetchrow) {
      print "<TR>\n";
      foreach $field (@vetor) {
         print "<TD VALIGN=TOP>$field</TD>\n";
      }
      print "</TR>\n";
   }

   print "</table>\n";

   $sth->finish;
   $dbh->disconnect();
} else {
   print "Cannot connect to Postgres server: $DBI::errstr\n";
   print " db connection failed\n";
}

NOTE: This has come to my attention since writing the above but I have not had time to
test it. The error checking above is lacking. Here’s what I think will work:

$sth = $dbh->prepare("SELECT * from table limit 20");
if (!defined($sth)) {
   print "Cannot prepare $DBI::errstr\n";
}
if (!$sth->execute) {
   print quot;Cannot execute $DBI::errstr\n";
}

2 thoughts on “PostgreSQL and Perl”

  1. Mohd Iqbal mohsin

    Is this right I set variable….

    # $Id: Makefile.PL,v 1.22 1999/09/29 20:30:23 mergl Exp $

    use ExtUtils::MakeMaker;
    use Config;
    use strict;
    use DBI 1.00;
    use DBI::DBD;

    print "Configuring Pg\n";
    print "Remember to actually read the README file !\n";

    my $POSTGRES_INCLUDE=’/usr/local/pgsql/include’;
    my $POSTGRES_LIB=’/usr/local/pgsql/lib’;

    if ((!$ENV{POSTGRES_INCLUDE} or !$ENV{POSTGRES_LIB}) and !$ENV{POSTGRES_HOME}) {
    die "please set environment variables POSTGRES_INCLUDE and POSTGRES_LIB !\n";
    } elsif ((!$ENV{POSTGRES_INCLUDE} or !$ENV{POSTGRES_LIB}) and $ENV{POSTGRES_HOME}) {
    $POSTGRES_INCLUDE = "$ENV{POSTGRES_HOME}/include";
    $POSTGRES_LIB = "$ENV{POSTGRES_HOME}/lib";
    } else {
    $POSTGRES_INCLUDE = "$ENV{POSTGRES_INCLUDE}";
    $POSTGRES_LIB = "$ENV{POSTGRES_LIB}";
    }

    my $os = $^O;
    print "OS: $os\n";

    my $dbi_arch_dir;
    if ($os eq ‘MSWin32’) {
    $dbi_arch_dir = "\$(INSTALLSITEARCH)/auto/DBI";
    } else {
    $dbi_arch_dir = dbd_dbi_arch_dir();
    }

    my %opts = (
    NAME => ‘DBD::Pg’,
    VERSION_FROM => ‘Pg.pm’,
    INC => "-I$POSTGRES_INCLUDE -I$dbi_arch_dir",
    OBJECT => "Pg\$(OBJ_EXT) dbdimp\$(OBJ_EXT)",
    LIBS => ["-L$POSTGRES_LIB -lpq"],
    AUTHOR => ‘Edmund Mergl (E.Mergl@bawue.de)’,
    ABSTRACT => ‘PostgreSQL database driver for the DBI module’,
    );

    if ($os eq ‘hpux’) {
    my $osvers = $Config{osvers};
    if ($osvers < 10) {
    print "Warning: Forced to build static not dynamic on $os $osvers.\a\n";
    $opts{LINKTYPE} = ‘static’;
    }
    }

    if ($Config{dlsrc} =~ /dl_none/) {
    $opts{LINKTYPE} = ‘static’;
    }

    WriteMakefile(%opts);

    exit(0);

    # end of Makefile.PL

Leave a Comment

Scroll to Top