PostgreSQL可以使用Perl DBI模块与Perl集成，Perl DBI模块是Perl编程语言的数据库访问模块。 它定义了一组提供标准数据库接口的方法，变量和约定。
以下是在Linux / Unix机器上安装DBI模块的简单步骤：
$ 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
如果您需要安装DBI的SQLite驱动程序，那么可以安装如下：
$ 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
在开始使用Perl连接PostgreSQL接口之前，请在PostgreSQL安装目录中找到pg_hba.conf文件，并添加以下行：
# IPv4 local connections:
host all all 127.0.0.1/32 md5
您可以启动/重新启动postgres服务器，可使用以下命令运行：
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
Perl连接到PostgreSQL数据库
以下Perl代码显示如何连接到现有的数据库。 如果数据库不存在，那么它将自动创建，最后将返回一个数据库对象。
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database successfully\n"
现在，我们运行上面的程序来打开数据库:testdb，如果成功打开数据库，那么它将给出以下消息：
Open database successfully
创建表
以下Perl程序将用于在之前创建的数据库(testdb)中创建一个表：
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database 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()
当执行上述程序时，它将在testdb中创建一张COMPANY表，并显示以下消息：
Opened database successfully
Table created successfully
插入操作
以下Perl程序显示了如何在上述示例中创建的COMPANY表中创建/插入记录：
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database successfully\n"
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, &aposPaul&apos, 32, &aposCalifornia&apos, 20000.00 ))
my $rv = $dbh->do($stmt) or die $DBI::errstr
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, &aposAllen&apos, 25, &aposTexas&apos, 15000.00 ))
$rv = $dbh->do($stmt) or die $DBI::errstr
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, &aposTeddy&apos, 23, &aposNorway&apos, 20000.00 ))
$rv = $dbh->do($stmt) or die $DBI::errstr
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, &aposMark&apos, 25, &aposRich-Mond &apos, 65000.00 ))
$rv = $dbh->do($stmt) or die $DBI::errstr
print "Records created successfully\n"
$dbh->disconnect()
当执行上述程序时，它将在COMPANY表中创建/插入给定的记录，并显示以下两行：
Opened database successfully
Records created successfully
SELECT操作
以下Perl程序显示了如何从上述示例中创建的COMPANY表中获取和显示记录：
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database 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()
执行上述程序时，会产生以下结果：
Opened database 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
更新操作
Perl代码显示了如何使用UPDATE语句来更新指定记录，然后从COMPANY表中获取和显示更新的记录：
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database 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()
执行上述程序时，会产生以下结果：
Opened database 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
删除操作
Perl代码显示了如何使用DELETE语句删除指定记录，然后从COMPANY表中获取并显示剩余的记录：
#!/usr/bin/perl
use DBI
use strict
my $driver = "Pg"
my $database = "testdb"
my $dsn = "DBI:$driver:dbname=$databasehost=127.0.0.1port=5432"
my $userid = "postgres"
my $password = "pass123"
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr
print "Opened database 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()
执行上述程序时，会产生以下结果：
Opened database 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