テーブル一覧作成スクリプト
psqlコマンドの出力をPerlで整形して、テーブル一覧（のHTML）を作る。添付画像のような感じ。
#ref(): File not found: "postgres.gif" at page "PostgreSQL/システム管理/テーブル一覧作成スクリプト"
#!/usr/bin/perl
use strict;
use Data::Dumper;
our %label = (
'character varying' => 'varchar',
'integer' => 'int',
);
# read db name or die
my $dbname = shift or die "usage: $0 dbname\n";
my $tbname = shift;
# set current date
my @lt = localtime;
my $date = sprintf "%04d/%02d/%02d %02d:%02d",
$lt[5]+1900,$lt[4]+1,$lt[3],$lt[2],$lt[1];
# get info of all tables
my @tables = get_tables($tbname);
# make html of tables info
my $t_html = make_table_html(@tables);
# build html
my $html;
{
local $/;
$html = <DATA>;
}
$html =~ s/<DBNAME>/$dbname/;
$html =~ s/<DATE>/$date/;
$html =~ s/<T_HTML>/$t_html/;
# show
print $html;
exit;
sub get_tables {
my $tbname = shift;
my $regex = make_tbname_regex($tbname);
my @tables;
foreach (`echo '\\dtv' | psql -t -A -F '<>' $dbname`){
my ($table_name, $table_type) = (split('<>',$_))[1,2];
next if $tbname && $table_name !~ /$regex/;
push @tables, {table_name => $table_name, table_type => $table_type};
}
return @tables;
}
sub make_tbname_regex {
my $str = shift;
my @str = split ',', $str;
foreach (@str) {
$_ =~ s/\*/\.\*/g;
}
sprintf '^%s$',join '|',@str;
}
sub make_table_html {
my (@tables) = @_;
my $cnt = 0;
my $html;
my $n_tables = 2;
my $td_percent = int (100 / $n_tables);
foreach (@tables){
$html .= sprintf "<table width=\"100%\"><tr>\n" if $cnt % $n_tables == 0;
$html .= sprintf "<td valign=\"top\" width=\"$td_percent%\">\n";
my $table_name = $_->{table_name};
$html .= sprintf "<table class=\"tbl\">\n";
$html .= sprintf '<tr><th colspan="3">%s</th></tr>'."\n",esc($table_name);
foreach (`echo '\\d $table_name' | psql -t -A -F '<>' $dbname`){
chomp;
my ($col_name, $col_type, $modi) = split('<>',$_);
$html .= sprintf '<tr><td class="col_name">%s</td>
<td class="col_type">%s</td><td class="modi">%s</td></tr>'."\n",
esc($col_name), esc(format_col_type($col_type)), esc(format_modi($modi));
}
$html .= sprintf "</table>\n";
$html .= sprintf "</td>\n";
$html .= sprintf "</tr></table>\n" if ($cnt + 1) % $n_tables == 0;
$cnt++;
}
if (my $remain = $n_tables - ($cnt % $n_tables)) {
for (my $i=0;$i<$remain;$i++) {
$html .= sprintf "<td></td>";
}
$html .= sprintf "</tr></table>\n"
}
return $html;
}
sub format_modi {
my $str = shift;
$str =~ s/nextval\(.*?\)/serial/g;
return $str;
}
sub format_col_type {
my $str = shift;
foreach (keys %label) {
$str =~ s/$_/$label{$_}/;
}
return $str;
}
sub esc {
my $str = shift;
$str =~ s/&/&/g;
$str =~ s/</</g;
$str =~ s/>/>/g;
$str =~ s/"/"e;/g;
return $str;
}
__DATA__
<html>
<head>
<style type="text/css">
body {
font-family: verdana,sans-serif;
background: #eee;
}
table.tbl {
width: 100%;
}
th {
background: #999;
color: #fff;
font-size: 90%;
border: 1px solid #999;
}
td td {
font-size: 90%;
vertical-align: top;
background: #ddd;
padding: 0 3px;
border: 1px solid #999;
empty-cells: show;
}
td.colname {
width: 35%;
}
td.modi {
width: 35%;
}
</style>
</head>
<body>
<h1><DBNAME></h1>
<div class="date"><DATE></div>
<T_HTML>
</body>
</html>
