DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Some Postgres In Perl Code

02.03.2006
| 1169 views |
  • submit to reddit
        // description of your code here

#!/usr/bin/perl

use DBI;

&GetTables;

my $dbh = DBI->connect("dbi:Pg:dbname=emery", "openbasin","rx9km7",{ RaiseError => 1, AutoCommit => 1 });
open FH, ">bigDel.sql";

foreach(@tables){
    my $sth =  $dbh->prepare("SELECT data_date FROM $_" );
    $sth->execute;

    while (@row=$sth->fetchrow_array){
        if($row[0] =~ /(\d\d\d\d-\d\d-\d\d) 23:59:\d\d/){
            my $nsth = $dbh->prepare("SELECT COUNT(*) AS count FROM $_ WHERE data_date = '$1 00:00:00'" );
            $nsth->execute || die "new died.\n";
            @row_ary  = $nsth->fetchrow_array;
            if($row_ary[0]){
                $dupe_tables{$_} = 1;
                print FH "DELETE FROM $_ WHERE data_date = '$1 23:59';\n";
            }
        }
    }
    $sth->finish;
}

close FH;
$dbh->disconnect;

print "The following tables have dupes:\n";
foreach(keys %dupe_tables){
    print "$_\n";
}

sub GetTables{
@tables = qw/
s400_day
behp_day
blac_day
blue_day
/;}