プログラムを介して行番号をつける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でやる方法は…うーん。。