ねもぷらす

ふぁいんでぃんぐねもの日記。プログラミングとか育児とか

プログラムを介して行番号をつけるTips

DBI、DBD::PG のリハビリを兼ねて書き書き。
まずはテスト用のテーブルを作成、てきとーにデータも突っ込んでおく。

create table test (
    id integer unique,
    name text,
    color text
);
insert into test values(1, 'hoge', 'red'),(2,'foo','green'),(3,'baa','yerrow');

DBD::Pgが入ってなかったのでインストール。

$ sudo port install p5-dbd-pg
--->  Fetching p5-version
--->  Attempting to fetch version-0.76.tar.gz from http://ftp.ucr.ac.cr/Unix/CPAN/modules/by-module/../by-authors/id/J/JP/JPEACOCK/
--->  Verifying checksum(s) for p5-version
--->  Extracting p5-version
--->  Configuring p5-version
--->  Building p5-version with target all
--->  Staging p5-version into destroot
--->  Installing p5-version 0.76_0
--->  Activating p5-version 0.76_0
--->  Cleaning p5-version
--->  Fetching p5-dbd-pg
--->  Attempting to fetch DBD-Pg-2.10.7.tar.gz from http://ftp.ucr.ac.cr/Unix/CPAN/modules/by-module/DBD
--->  Verifying checksum(s) for p5-dbd-pg
--->  Extracting p5-dbd-pg
--->  Configuring p5-dbd-pg
--->  Building p5-dbd-pg with target all
--->  Staging p5-dbd-pg into destroot
--->  Installing p5-dbd-pg 2.10.7_0
--->  Activating p5-dbd-pg 2.10.7_0
--->  Cleaning p5-dbd-pg

くわーっと書いてみたスクリプト。OUTPUT の部分で行番号が入れられる。

#!/usr/bin/env perl -w
use strict;
use DBI;
use Data::Dumper;

# init
my $dsn      = 'dbi:Pg:dbname=postgres';
my $user     = 'postgres';
my $password = 'postgres';
my $sql      = "SELECT * FROM test ORDER BY id DESC";

eval {
    # connect DB
    my $dbh = DBI->connect(
        $dsn,
        $user,
        $password,
        { RaiseError => 1, AutoCommit => 0 }
    ) or die;

    # Do SQL
    my $sth = $dbh->prepare($sql);
    my $result = $sth->execute() or die;

    # Output
    my $count = 0;
    print "count id name color\n";
    while ( my $hash_ref = $sth->fetchrow_hashref ) {
        ++$count;
        printf ("%d %d %s %s\n",
                $count,
                $hash_ref->{id},
                $hash_ref->{name},
                $hash_ref->{color} );
    }

    # disconnect DB
    $sth->finish;
    $dbh->disconnect or die;
};
if ( $@ ) {
    print "Exception: $@\n";
}

SQLでやる方法は…うーん。。