Howdy:
First off, many thanks to all (esp. R. Dixon and T. Lowery)
for the help with the 'using table_info()' thread.
I'm just about done with that script, but I have
one tiny problem.
In my SECOND while loop, I place a comma (,) at the end
so that when the *.ddl files are built, it looks
just like I've done it by hand. And close to the
way I want to use the scripts for porting to Oracle.
Almost.
I have one comma too many and I would like to know
how can I either:
1) go back over the files AFTER they're done and
remove the very last comma in the file
2) set up the loop statement so that I only put
commas on all of the columns except for the last row
of data.
Are either of these possible?
I am including the script in case anyone is interested
in how it's set up. Yes, I know it could be shorter
and much, much cleaner - please feel free to muck
with it and pass on hints to making it better.
[snip]
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use POSIX 'strftime';
use Cwd;
# hey! this works!
# 02 Jul 03 - 1551EST
# script to get a list of tables from
# PostgreSQL and then get the table
# structure
# thought I had a copy of this somewhere else ...
# 2 Jul 03 -X
# set up some variables
my $datestr=strftime '%d%B%Y',localtime;
# connect to the database
my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'joe_user')
or die "Can not connect: $!";
# set up query
my $sql = qq|
SELECT tablename, tableowner
FROM pg_tables
where tablename like 't_%'
order by 1
|;
# test the database handler and prep the sql statements for
# execution
my $sth=$dbh->prepare($sql) or die "Error =", DBI::errstr;
# check for errors
unless ($sth->execute) {
print"\n\tExecute failed for stmt:\n\t$sql\nError = ", DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
while ( my($table, $tableowner)=$sth->fetchrow ) {
open (FILE, ">$tableowner\.$table.ddl");
print FILE "connect $tableowner/$tableowner;\n\n";
print FILE "create table $table (\n";
print "Starting ddl build of $table...\n";
# statement
my $statement = "
SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a
WHERE c.relname = '$table'
AND a.attnum > 0 AND a.attrelid = c.oid
AND c.relkind = 'r'
AND c.relname not like 'pg_%'
ORDER BY a.attnum, c.relname
";
# prep and execute the SQL statement
my $sth = $dbh->prepare ($statement);
$sth->execute();
while ( my($first, $second)=$sth->fetchrow ) {
print FILE "$first\t\t$second,\n";
}
print FILE ")\n";
print FILE ";\n";
print FILE "END;\n";
}
print "Done.\n";
close (FILE);
$dbh->disconnect;
__END__
[/snip]
Thanks!
-X
