Perl connect/access PostgreSQL

底下說明了Perl如何連接PostgreSQL和操作sql。

事前準備
範例：
1. load module
2. 初始 database handle
3. connect to database
4. 執行SQL
5. 取得結果列數
6. 處理每一row資料

1. load module

use DBI;

2. 初始 database handle

my $db_driver = 'Pg';
my $db_name = 'yourDB';
my $db_host = 'localhost';
my $db_user = 'yourDBUser';
my $db_pass = 'yourDBPass';
my $db_port = '5432';
my $db_url = "dbi:${db_driver}:dbname=${db_name};host=${db_host};port=${db_port};"

3. connect to database

my $dbh = DBI->connect($db_url, $db_user, $db_pass) or die "Can't connecting to the database: $DBI::errstr\n";

4. 執行SQL

my $sql = "SELECT * FROM yourTable';
my $sth = $dbh->prepare($sql);
$sth->execute;

5. 取得結果列數

my $rows = $sth->rows;

6. 處理每一row資料

while ($row = $sth->fetchrow_hasherf()) {
    my ($field1, $field2, ...) = ($row->{field1_name}, $row->{field2_name}, ...);
    ...
}