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";
}