Howdy:
I am trying to write a script to get table
info from PostgreSQL. Basically, I want to
generate a list of tables from a sub function
and pass that and then use that returned
variable and put it inside a NEW sql loop
and create more output files based.
My problem is (and I'm sure there are a lot of
them) I can't seem to return the list outside
the sub function and have that variable used again.
How do I do that?
I mean, I can see that it's dying at the very
end of the 'unless' statement at the bottom
of the script, but I don't know *why* ...
I'm not sure I understand what the error is
trying to say.
The table exist and I can select from that,
too.
The errors that I am getting are:
[snip errors]
DBD::Pg::st execute failed: ERROR: Attribute
't_prof_inp_det_combined' not found at ./getpg_sch.pl line 58.
Execute failed for stmt:
SELECT a.attname, format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a
WHERE c.relname = t_prof_inp_det_combined
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
Error = ERROR: Attribute 't_prof_inp_det_combined' not found
Uncaught exception from user code:
Clean up finished
[/snip errors]
---
[snip script]
#!/usr/bin/perl -w
# created 26 Jun 03 -X
# script to connect to Postgres do a count
# get a list of tables info (DDL) and make
# new DDL files to move to another DB
#
# need table owner, table name, column, type
# and pass that into a file
#
# should be cool
use strict;
use diagnostics;
use DBI;
use POSIX 'strftime';
our $qual=$qual;
our $owner=$owner;
our $name=$name;
our $type=$type;
our $rem=$rem;
my $datestr=strftime '%d%B%Y',localtime;
# connect to postgres via DBI
my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'joeuser')
or die "Can not connect: $!";
&getTable();
# without the sub(), this works by
# itself - but I want the results of
# the query from $sql to be appended into
# each $name.dll file
#
sub getTable() {
my $tabsth = $dbh->table_info();
while (my ($qual, $owner, $name, $type, $rem)=
$tabsth->fetchrow_array() ) {
open (FILE, ">$name.dll") or die "Snootch-to-the-nootch\n";
print FILE "$name\n";
return $name;
}
}
# this works if i put in an actual
# table name; but i want to put the list
# of $name inside this to loop and
# generate $name.ddl files
# how can i do that?
#
my $sql= qq|
SELECT a.attname, format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a
WHERE c.relname = $name
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
|;
my $sth=$dbh->prepare($sql) or die "Error =",DBI::errstr;
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 ($tablename, $type)=
$sql->fetchrow_array() ) {
print FILE "\n";
print FILE "$tablename\t$type\n";
}
close (FILE);
$dbh->disconnect;
__END__
[/snip script]
Thanks!
-X
