#! /usr/bin/perl -w
#
# col-retyper -- change the type of a Postgresql column
#
# David Landgren, copyright (C) 2008-2010
# This program is free software; you can redistribute it and/or modify it
# under the same terms as Perl itself.
#
# version 1.1 - bug fixed in column comment management

use strict;
use warnings;
use DBI;

my $db = DBI->connect(
    'dbi:Pg:dbname=xxx;host=yyy', 'me', 'sekret',
    {AutoCommit => 0},
) or die;
END { $db and $db->disconnect; }

my $table = shift or die "No table name given\n";
my $col   = shift or die "No column name given\n";
my $type  = shift or die "No new type given, e.g. 'varchar(2)'\n";

my ($tableoid, $columnoid) = $db->selectrow_array(<<SQL_END);
select
     a.attrelid as "tableoid"
    ,a.attnum   as "columnoid"
from
    pg_class c
    join pg_attribute a on c.oid = a.attrelid
where
        c.relname  = '$table'
    and a.attname  = '$col'
    and a.attnum   > 0
SQL_END

if (!(defined $tableoid and defined $columnoid)) {
    die "Failed to obtain oids for $table.$col, is there a typo?";
}

my $comment = $db->selectrow_array(
    "select col_description($tableoid, $columnoid)"
) || '';

my @update = (
    "alter table $table add column tmp_xxx_$col $type",
    "update $table set tmp_xxx_$col = cast($col as $type)",
    "alter table $table drop column $col",
    "alter table $table add column $col $type",
    ("comment on column $table.$col is '$comment'") x!! length $comment,
    "update $table set $col = tmp_xxx_$col",
    "alter table $table drop column tmp_xxx_$col",
);

for my $cmd (@update) {
    print "$cmd\n";
    $db->do($cmd) or die "Could not run [$cmd]: ", $db->errstr;
}
$db->commit;