PostgreSQL Notes
Prev	Chapter 9. Using Perl	Next

Using the Pg module

You want to get started with the Pg module, but need an example.

Solution

Using Pg to access your database has several steps:

Connect to the database (local or remote, with or without username and password).
Build and execute your SQL query.
Loop fetching rows (assuming you have a SELECT query).
Check return codes for all of the above steps.

You can find out more about the Pg module by running perldoc Pg.

Discussion

Below is an example script that parses a text-file for diary entries and inserts them to a database. It then calculates a top 10 of companies based on how many diary entries they have.

#!/usr/bin/perl -w
use strict;

use Pg;

# Config settings
#
my $dbname = 'pgtest';

# Connect to database
#
my $conn = Pg::connectdb("dbname=$dbname");
if ($conn->status != PGRES_CONNECTION_OK) {
    die "ERR: Couldn't open connection: ".$conn->errorMessage."\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;
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 $sql = "INSERT INTO diary (dy_company, dy_type, dy_notes) ".
              "VALUES ($co_id, '$dy_type', '$dy_notes')";
    my $res = $conn->exec($sql);
    if ($res->resultStatus != PGRES_COMMAND_OK) {
        die "Failed to insert entry number $i: ".-$conn->errorMessage."\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(*)
FROM diary 
GROUP BY dy_company 
ORDER BY count(*) DESC LIMIT $top
TOP10

# Execute the query
#
my $res = $conn->exec($sql);
if ($res->resultStatus != PGRES_TUPLES_OK) {
    die "Top $top query failed: ".$conn->errorMessage."\n";
}

# Check how many results we got
#
my $numres = $res->ntuples;
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 = $res->fetchrow;
    print "$i\t$row[0]\t$row[1]\n";
}
exit;

__DATA__
100|TEST|This is a test
101|TEST|Another test
102|TEST|Third test entry
103|TEST|Final test entry

Note that in the above example we need to quote the text fields in our INSERT statement. If the fields contain a single quote we'll need to escape it.

Also note that $res->fetchrow returns an array. This means you need to know the order of the columns in your result set. If you wanted to put the array into a hash with column-names you could do something like this:

my @columns = qw(dy_company, num_entries);
for (my $i=1; $i<=$numres; $i++) {
    my @row = $res->fetchrow;
    my $c=0;
    my %row_hash = map { $columns[$c++] => $_ } @row;
    print "$i\t",$rowhash{dy_company},"\t",$rowhash{num_entries},"\n";
}

The experienced will notice we can do away with @row, but I've left it in for clarity. The other thing you might notice is that it is easy for the array of column-names and the text of the query to get out of step. You might want to consider inserting the array into the query-text to avoid this.