Perl and PostgreSQL – Selecting Data

When performing an operation on a PostgreSQL database, such as selecting, inserting, updating and deleting data, the first thing that needs to be done is to connect to the database, as previously described.

In order to retrieve data, as well as insert, update and delete data, from a PostgreSQL database, SQL, or Structured Query Language needs to be used, more details of which can be found here. Retrieving data is done via the ‘Select’ statement.

The following table of data, called ‘person’, will be used in the example below for selecting data.

id	firstname	lastname	title	dob
1	Bob	Smith	Mr	1980-01-20
2	George	Jones	Mr	1997-12-15
3	Fred	Bloggs	Mr	1975-05-07
4	Alan	White	Mr	1989-03-20

Note that by default, dates in a PostgreSQL database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

The example below selects four items of data from the ‘person’ table, in last name, first name and date of birth order. The resulting data is stored in a variable, which is then used in a ‘while‘ loop to output details of each record to the terminal in the format: “id: lastname, firstname (dob)”. There are two extra 'use' statements to deal with the formatting and parsing of dates.

use strict;
use warnings;
use Date::Format;
use Date::Parse;
use DBI;

# Database connection variable.
my $connect;

eval 
{

    # Connect to database.
    $connect = DBI->connect("DBI:Pg:dbname = demo; host = localhost; port = 5432", 
                            "DemoUN", "DemoPW", {RaiseError => 1});

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

eval
{

    # Date template.
    my $template = "%d/%m/%Y";

    # Query text.
    my $sqlText = " \
        SELECT id, firstname, lastname, dob \
        FROM person \
        ORDER BY lastname, firstname, dob \
    ";

    # Prepare the query.
    my $sqlSelect = $connect->prepare($sqlText);

    # Execute the query.
    $sqlSelect->execute();

    # Display person information in the terminal.
    while (my @row = $sqlSelect->fetchrow_array())
    {

        # Format the date of birth.
        my $dobFormatted = time2str($template, str2time($row[3]));

        # Display a formatted string of person information.
        print "$row[0]: $row[2], $row[1] ($dobFormatted)\n"

    }

    # Clean up.
    $sqlSelect->finish();
    $connect->disconnect();

}
or do
{

    # Confirm error retrieving person information and exit.
    print "Error retrieving person information.\n";
    exit(1);

}

The resulting output to the terminal is as follows.

3: Bloggs, Fred (07/05/1975)
2: Jones, George (15/12/1997)
1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

Often it isn’t necessary to return all records from a database table. Where this is the case, parameters need to be introduced into the query. In the following example, the records returned are limited to those with a date of birth between 1 January 1980 and 31 December 1989. Within the SQL question marks are used to signify that parameters need to be incorporated. The parameter values are then bound into the SQL statement when it is executed. Binding the parameters in this way helps prevent SQL injection, where hackers try to insert malicious code to either do damage to the database or access more data than should be allowed. A further 'use' statement, 'DateTime', is required to handle the date range parameters.

use strict;
use warnings;
use Date::Format;
use Date::Parse;
use DateTime;
use DBI;

# Database connection variable.
my $connect;

eval 
{

    # Connect to database.
    $connect = DBI->connect("DBI:Pg:dbname = demo; host = localhost; port = 5432", 
                            "DemoUN", "DemoPW", {RaiseError => 1});

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

eval
{

    # Date template.
    my $template = "%d/%m/%Y";

    # Query parameters.
    my $dobLower = DateTime->new(
        year => 1980,
        month => 1,
        day => 1,
        hour => 0,
        minute => 0,
        second => 0
    );
    my $dobUpper = DateTime->new(
        year => 1989,
        month => 12,
        day => 31,
        hour => 23,
        minute => 59,
        second => 59
    );

    # Query text.
    my $sqlText = " \
        SELECT id, firstname, lastname, dob \
        FROM person \
        WHERE dob BETWEEN ? AND ?
        ORDER BY lastname, firstname, dob \
    ";

    # Prepare the query.
    my $sqlSelect = $connect->prepare($sqlText);

    # Execute the query.
    $sqlSelect->execute($dobLower, $dobUpper);

    # Display person information in the terminal.
    while (my @row = $sqlSelect->fetchrow_array())
    {

        # Format the date of birth.
        my $dobFormatted = time2str($template, str2time($row[3]));

        # Display a formatted string of person information.
        print "$row[0]: $row[2], $row[1] ($dobFormatted)\n"

    }

    # Clean up.
    $sqlSelect->finish();
    $connect->disconnect();

}
or do
{

    # Confirm error retrieving person information and exit.
    print "Error retrieving person information.\n";
    exit(1);

}

The resulting output to the terminal is as follows.

1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

Perl and PostgreSQL – Selecting Data

More