#!/usr/bin/perl -w

use DBI;
use Text::CSV_XS;
use File::Basename;

my $csvFile = shift or die "Usage: $0 <file.csv>\n";
open(CSV,$csvFile) or die "$0: Can't open $csvFile for read\n";

my ($tableName) = fileparse($csvFile,qr{\..*});
warn "tableName=$tableName\n";

my $dbh = DBI->connect(
  'dbi:Pg:dbname=dbname', 'user', 'pass',
  { AutoCommit => 0 }
);
my $csv = Text::CSV_XS->new();

# create the table based on names from the header row
# columns will arbitrarily be of type VARCHAR(20)
my $header = <CSV>;
$csv->parse($header) or die "$0: parse of header row failed\n";
my @columns = $csv->fields();
my $sql = "CREATE TABLE $tableName(";
foreach my $column (@columns) {
  $sql .= "\n  $column VARCHAR(20),";
}
chop($sql);                     # kill the trailing comma
$sql .= "\n)";
warn "\n$sql\n";
$dbh->do($sql);

# add rows of data using COPY
$dbh->do("COPY $tableName FROM STDIN WITH DELIMITER AS ','");
my $rowCount = 0;
while(<CSV>) {
  $dbh->pg_putline($_);
  $rowCount++;
}
$dbh->pg_endcopy;
$dbh->commit;
$dbh->disconnect;

warn "$0: Added $rowCount rows to $tableName\n";