You'd like to make it as easy as possible to port your application to a number of different databases, or you just want a higher-level interface than Pg offers. Use the DBI system.
Perl's DBI/DBD system is a set of modules that provide (as far as possible) a vendor-neutral interface to your database. The DBI modules provide the interface while the DBD modules provide a backend driver. There are DBD drivers for everything from CSV to Oracle - I cannot recommend the system highly enough.
You connect, execute queries and fetch results as for Pg, but there are some additional features. You can "prepare" an SQL statement then execute it with varying parameters. You can also fetch results as an array, arrayref or hashref.
You can find out more about DBI with perldoc DBI and about PostgreSQL specific factors with perldoc DBD::Pg.
The example below performs the same tasks as the Pg example.
#!/usr/bin/perl -w
use strict;
use DBI;
# Config settings
#
my $dbname = 'pgtest';
# Connect to database
#
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname");
if (!$dbh) {
die "ERR: Couldn't open connection: ".$DBI::errstr."\n";
}
# Loop through the DATA section at the end of the file
# reading in diary entries and insert them to the database
#
my $i=0;
my $insert_sql = "INSERT INTO diary (dy_company, dy_type, dy_notes) VALUES (?,?,?)";
while (my $line = <DATA>) {
$i++;
chomp $line;
# Separate fields on |
#
my ($co_id,$dy_type,$dy_notes) = split /\|/, $line;
# Build and run the query
#
my $numrows = $dbh->do($insert_sql, undef, $co_id, $dy_type, $dy_notes);
if (!$numrows) {
die "Failed to insert entry number $i: ".$dbh->errstr."\n";
}
}
print "Inserted $i diary entries\n\n";
# Now lets print out a top-10 of companies with diary entries
#
my $top = 10;
print "The top $top companies for diary entries are as follows:\n";
# Build our top-10 query using a here document this time.
#
my $sql = <<"TOP10";
SELECT dy_company, count(*) AS numentries
FROM diary
GROUP BY dy_company
ORDER BY numentries DESC LIMIT ?
TOP10
# Execute the query
#
my $sth = $dbh->prepare($sql);
my $res = $sth->execute($top);
if (!$res) {
die "Top $top query failed: ".$dbh->errstr."\n";
}
# Check how many results we got
#
my $numres = $sth->rows;
if ($numres != $top) {
warn "WARNING: Top $top query returned $numres results\n";
}
# Print out our results
#
print "Posn\tCo-id\tNum entries\n",'-' x 30,"\n";
for (my $i=1; $i<=$numres; $i++) {
my $row = $sth->fetchrow_hashref;
print "$i\t".$row->{dy_company},"\t",$row->{numentries},"\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__DATA__
100|TEST|This is a test
101|TEST|Another test
102|TEST|Third test entry
103|TEST|Final test entry
|
Notice how when building queries we can use ? as a place-holder and then bind a list or array of values to them. The DBI system handles quoting for us (neat eh?)