#!/usr/bin/perl

use strict;
use DBI;
use Encode;
use Data::Dumper;

my $msdb = DBI->connect('dbi:ODBC:odbcname','login','password');
$msdb->{odbc_exec_direct} = 1;

my $pgdb = DBI->connect("dbi:Pg:dbname=databasename;host=localhost;sslmode=require;", "login", "password",{AutoCommit => 0, pg_enable_utf8 => 1});

# add a call to migrateTable here for every table in the database you want to migrate
# 3 arguments:
# - name of table
# - array of field names
# - name of primary key field for which you created a sequence ("null" if there is not any)
migrateTable ("tablename", ["field1", "field2", "field3"], "field1");

sub migrateTable {
        my $tableName = shift;
        my $columnNames = shift;
        my $sequenceCol = shift;
        my $msSql = "select ";
        my $columnCount = 0;
        my $pgSql = 'insert into "' . lc($tableName) . '" (';
        foreach my $column (@{$columnNames}) {
                if ($columnCount != 0) {
                        $msSql .= ", ";
                        $pgSql .= ", ";
                }
                $msSql .= $column;
                $pgSql .= '"' . lc($column) .'"';
                ++$columnCount;
        }
        $msSql .= " from " . $tableName;
        $pgSql .= ") values (";
        my $counter = 1;
        while ($counter < = $columnCount) {
                if ($counter != 1) {
                        $pgSql .= ", ";
                }
                $pgSql .= " ?";
                ++$counter;
        }
        $pgSql .= ")";
        my $msQuery = $msdb->prepare($msSql) || die ($msSql . "\n");
        my $pgQuery = $pgdb->prepare($pgSql);
        $msQuery->execute() || die ($msSql . "\n");

        while (my @data = $msQuery->fetchrow_array()) {
                for (my $i = 0; $i < $columnCount; ++$i) {
                        $data[$i] = Encode::encode("utf8", Encode::decode("iso8859-15", $data[$i]));
                        if ($data[$i] eq "" || $data[$i] eq " ") {
                                $data[$i] = undef;
                        }
                        # remove leading and trailing white space
                        $data[$i] =~ s/^\s+//;
                        $data[$i] =~ s/\s+$//;
                }
                $pgQuery->execute(@data) || die ($pgSql . "\n" . Dumper @data);
        }
        if ($sequenceCol != "null") {
                $pgSql = 'select setval (\'"' . lc($sequenceCol) . '_seq"\', max("' . $sequenceCol . '")) from "' . lc($tableName) . '"';
                $pgQuery = $pgdb->prepare($pgSql);
                $pgQuery->execute;
        }
        $pgdb->commit;
}