Basic PostgreSQL Tutorial

Advanced PostgreSQL Tutorial

PostgreSQL Interface

PostgreSQL Perl Interface

Installation

PostgreSQL can be integrated with Perl using the perldbi module, which is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a standard database interface.

Below are the simple steps on your Linux/Simple steps to install the DBI module on Unix machines

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

If you need to install the SQLite driver for DBI, you can install it in the following way

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz
$ cd DBD-Pg-2.19.3
$ perl Makefile.PL
$ make
$ make install

Before starting to use the Perl PostgreSQL interface, find the pg_hba.conf file under the PostgreSQL installation directory and add the following line:

#　IPv4　local connections:
host　　　　all　　　　　　　　　all　　　　　　　　　127.0.0.1/32　　　　　　　　　　md5

If the postgres server is not running, you can use the following command to start it/Restart the postgres server-

[root@host]# service postgresql restart
Stopping postgresql service:　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　[　　OK　　]
Starting postgresql service:　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　[　　OK　　]

DBI Interface APIs

Below are some important DBI routines that can meet your needs for using SQLite databases in Perl programs. If you are looking for more complex applications, you can refer to the official Perl DBI documentation.

S. No.	API & Description
1	DBI→connect($data_source, "userid", "password", \%attr) Establish a database connection or session to the requested $data_source. If the connection is successful, return a database handle object. The format of Datasource is: DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg is the name of the PostgreSQL driver, and testdb is the name of the database.
2	$dbh→do($sql) This routine prepares and executes an SQL statement. Returns the number of affected or undefined rows when an error occurs. Return value-1Indicates that the number of rows is unknown, not applicable, or not available. Here, $dbh is the handle returned by the DBI→connect() call.
3	$dbh→prepare($sql) Prepare a statement for later execution by the database engine and return a reference to the statement handle object.
4	$sth→execute() Perform any processing required to execute a prepared statement. If an error occurs, return undefined. A successful execution always returns true, regardless of the number of affected rows. Here, $sth is a statement handle returned by the call to $dbh→prepare($sql).
5	$sth→fetchrow_array() Retrieve the next line of data and return it as a list containing field values. Empty fields are returned as undefined values in the list.
6	$DBI::err Is equivalent to $h->err, where $h is any handle type, such as $dbh, $sth, or $drh. This will return the native database engine error code from the last called driver method.
7	$DBI::errstr Is equivalent to $h->errstr, where $h is any handle type, such as $dbh, $sth, or $drh. This will return the native database engine error message from the last called DBI method.
8	$dbh->disconnect(); Closed the database connection previously opened by calling DBI->connect().

Database Connection

The following Perl code shows how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host =　127.0.0.1;port =　5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}　
　　　or die $DBI::errstr;
print "Database opened successfully\n";

Now, let's run the above given program to open the database testdb; if the database is successfully opened, it will give the following message-

Database opened successfully

Create Table

The following Perl program will be used to create a table in the previously created database

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}
　　　or die $DBI::errstr;
print "Database opened successfully\n";
my $stmt = qq(CREATE TABLE COMPANY
　　　　　　(ID INT PRIMARY KEY NOT NULL,
　　　　　　NAME TEXT NOT NULL,
　　　　　　AGE INT NOT NULL,
　　　　　　ADDRESS CHAR(50),
　　　　　　SALARY REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
　　　print $DBI::errstr;
} else {
　　　print "Table created successfully\n";
}
$dbh->disconnect();

When the above given program is executed, it will create the COMPANY table in testdb and display the following message-

Database opened successfully
Table created successfully

INSERT Operation

The following Perl program demonstrates how to create records in the COMPANY table created above-

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host =　127.0.0.1;port =　5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}
　　　or die $DBI::errstr;
print "Database opened successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
　　　VALUES (1, 'Paul',　32, 'California',　20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
　　　VALUES (2, 'Allen',　25, 'Texas',　15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
　　　VALUES (3, 'Teddy',　23, 'Norway',}}　20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
　　　VALUES (4, 'Mark',　25, 'Rich-Mond',　65000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();

When the above program is executed, the specified records will be created in the COMPANY table and the following two lines will be displayed

Database opened successfully
Records created successfully

SELECT operation

The following Perl program shows how to retrieve and display records from the COMPANY table created in the above example-

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host =　127.0.0.1;port =　5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}
　　　or die $DBI::errstr;
print "Database opened successfully\n";
my $stmt = qq(SELECT id, name, address, salary FROM COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
　　　print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
　　　　　　print "ID = " . $row[0] . "\n";
　　　　　　print "NAME = " . $row[1]."\n";
　　　　　　print "ADDRESS = " . $row[2]."\n";
　　　　　　print "SALARY = " . $row[3]."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, the following results will be produced

Database opened successfully
ID =　1
NAME = Paul
ADDRESS = California
SALARY =　　20000
ID =　2
NAME = Allen
ADDRESS = Texas
SALARY =　　15000
ID =　3
NAME = Teddy
ADDRESS = Norway
SALARY =　　20000
ID =　4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =　　65000
Operation done successfully

UPDATE operation

The following Perl code demonstrates how to use the UPDATE statement to update any records and then retrieve and display the updated records from our COMPANY table:

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host =　127.0.0.1;port =　5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}
　　　or die $DBI::errstr;
print "Database opened successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY =　25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
　　　print $DBI::errstr;
} else {
　　　print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary FROM COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
　　　print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
　　　　　　print "ID = " . $row[0] . "\n";
　　　　　　print "NAME = " . $row[1]."\n";
　　　　　　print "ADDRESS = " . $row[2]."\n";
　　　　　　print "SALARY = " . $row[3]."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, the following results will be produced

Database opened successfully
Total number of rows updated :　1
ID =　1
NAME = Paul
ADDRESS = California
SALARY =　　25000
ID =　2
NAME = Allen
ADDRESS = Texas
SALARY =　　15000
ID =　3
NAME = Teddy
ADDRESS = Norway
SALARY =　　20000
ID =　4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =　　65000
Operation done successfully

DELETE operation

The following Perl code demonstrates how to use the DELETE statement to delete any records and then retrieve and display the records from the COMPANY table-to retrieve and display the remaining records

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";　
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host =　127.0.0.1;port =　5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError =>　1　})}}
　　　or die $DBI::errstr;
print "Database opened successfully\n";
my $stmt = qq(DELETE FROM COMPANY WHERE ID=)2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
　　　print $DBI::errstr;
} else{
　　　print "Total number of rows deleted: $rv\n";
}
$stmt = qq(SELECT id, name, address, salary FROM COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
　　　print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
　　　　　　print "ID = " . $row[0] . "\n";
　　　　　　print "NAME = " . $row[1]."\n";
　　　　　　print "ADDRESS = " . $row[2]."\n";
　　　　　　print "SALARY = " . $row[3]."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, the following results will be produced

Database opened successfully
Total number of rows deleted:　1
ID =　1
NAME = Paul
ADDRESS = California
SALARY =　　25000
ID =　3
NAME = Teddy
ADDRESS = Norway
SALARY =　　20000
ID =　4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =　　65000
Operation done successfully

PostgreSQL Python PostgreSQL PHP