RE: problem opening file inside for-loop

[Johnson, Shaunn]

--Rob

--Thanks for your script!  Looks like it works like
--a champ!

--However, I don't have a clear understanding
--as to *how* it works.  Specifically, how is the
--'build regex' being applied inside the SQL?  
--I mean, how is it getting IN there?
--I'm looking at it and I don't fully grok the technique.

--Can someone point me to a primer that could
--give a once-over about how this is actually
--working?

--Thanks again!

-X

-----Original Message-----
From: Rob Dixon [mailto:[EMAIL PROTECTED]
[snip stuff]

  #!/usr/bin/perl
  use strict;
  use warnings;

  use DBI;

  # creating an alternative backup strategy for
  # the database - gonna be ugly ...

  # create a few variables
  my $addr =      '[EMAIL PROTECTED]';
  #my $outfile =  `date +%d%b%Y`;
  my $outfile =   `date | cut -f 1 -d ' '`;
  my $datetype =  `date`;
  my $file =      '/usr/local/home/joe/tmp/backup_list.txt';
  my $matchday =  `date +%a`;

  my %backup;
  @backup{ qw / Sun Mon Tue Wed Thu Fri Sat / } = qw/ a-c d e-o p q-r s t-z
/;

  # Define the connection to the database and user or give up
  #
  my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'postgres')
      or die "Can not connect: $!";

  # Create an SQL to get the initial list of tables
  #
  my $sth=$dbh->prepare(<<END) or die "Error =", DBI::errstr;
    select relname
    from pg_class
    where relname ~ ?
    and relkind = 'r'
    and relname not like 'pg_%'
    order by 1;
  END

  # Build the regex for today
  #
  my $range = $backup{$matchday};
  $range = sprintf "^[%s%s]", uc $range, lc $range;

  # Fetch the data and die if we failed
  #

  my $rows = $sth->execute($range);

  unless ($rows) {
    print"\n\tExecute failed\nError = ", DBI::errstr;
    $sth->finish;
    $dbh->disconnect;
    die "\n\t\tClean up finished\n";
  }

  # Open the log file and print the header
  #
  open FILE, ">$file" or die "Can nae open $file: $!\n";

  print FILE "Today is: $datetype\n\n";
  print FILE "These are the tables that have been backed up:\n\n";

  # The work - this is the loop
  #
  while ( my $table = $sth->fetchrow ) {
    print "Backing up $table...\n";
    #print `echo 'postgres
    #
    # ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
    print FILE "$table", "\n\n";
    print FILE "Please review\n\n";
  }

  # All done
  #
  close FILE;
  $dbh->disconnect;

  # Maybe i should mail something to me anyway
  #
  my $sendmailtable = "/usr/sbin/sendmail $addr < $file";
  print `$sendmailtable`;

  __END__


[/snip stuff]