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