column-types.pl

use DBI;
use strict;
use warnings;

my $dbh;

sub db_connect {
	my $usename = (getpwuid $>)[0];
	$dbh ||= DBI->connect("dbi:Pg:dbname=$usename", $usename, "") or die DBI->errstr();
	$dbh;
}

sub table_columns {
	my $table = $_[0];
	my @columnlist;
	my $cih = $dbh->column_info(undef, undef, $table, undef) or die "no table $table";
	my $i = 0;
	while (my $chr = $cih->fetchrow_hashref) {
		my $cn = $chr->{COLUMN_NAME};
		$cn =~ s/"//g;
		push @columnlist, [$cn, $chr->{TYPE_NAME}];
	}
	return \@columnlist;
}

sub query_columns {
	my $query = $_[0];
	my $sth = $dbh->prepare($query) or die "query $query error: " . $dbh->errstr;
	$sth->execute or die "query $query error: " . $dbh->errstr;
	my @columnlist;
	for (my $cn = 0; $cn < @{$sth->{NAME}}; ++$cn) {
		my $ti = $dbh->type_info($sth->{TYPE}->[$cn]);
		my $cn = $sth->{NAME}->[$cn];
		$cn =~ s/"//g;
		push @columnlist, [$cn, $ti->{TYPE_NAME} // 'UNKNOWN'];
	}
	return \@columnlist;
}

sub print_columns {
	my ($name, $cref) = @_;
	print "\n$name:\n";
	for my $ci (@$cref) {
		print "$ci->[0]: $ci->[1]\n";
	}
}

#anton=> \d todo
#                           Table "public.todo"
#  Column   |  Type   |                     Modifiers
#-----------+---------+---------------------------------------------------
# id        | integer | not null default nextval('todo_id_seq'::regclass)
# title     | text    |
# completed | boolean |
# misc      | jsonb   |
#Indexes:
#    "todo_pkey" PRIMARY KEY, btree (id)

db_connect;

print_columns("todo",               table_columns("todo"));
print_columns("select * from todo", query_columns("select * from todo"));

#output:
#
#todo:
#id: integer
#title: text
#completed: boolean
#misc: jsonb
#
#select * from todo:
#id: int4
#title: UNKNOWN
#completed: bool
#misc: unknown