Perl script to drop duplicated constraints definitions

Hi,

Sorry for the recent post, mistakenly i wrote the subject in spanish... glup.

Hi,

Sometimes i end up with some duplicated constraints definitions in my database, i've noticed this when i reverse
engineerdatabases, and see many links between two tables. 

I prepared a perl script that read an schema on standard input, and prints on standard output some drop constraints for
duplicateddefinitions, if you like you can then execute them against your database: 

For example if you save it as drop-dup-constraints.pl, then you can check your database with:

pg_dump  -Ox -s mydatabase | drop-dup-constraints.pl


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

## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);

my %tuplas;

my @lines = ();

while (<>) {

    next if /--/;

    chomp;
    push @lines, $_;
    # Ensamblar el sql acumulado e imprimir
    if (/;/) {

        &processLine(join " ", @lines);
        @lines = ();

    }

}

# Results

TUPLA: while (my ($k,$v) = each %tuplas) {

    next TUPLA if @$v == 1;

#        print "$k\n";

    my @arr = @$v;
    shift @arr; # let the first constraint
    my ($table) = split /,/, $k;
    foreach my $constraint (@arr) {
    # print "\t$_\n";

        printf "alter table %s drop constraint %s;\n", $table, $constraint;

    }

}

exit 0;

my $lastSchema = "public";

sub processLine {

local $_ = shift;
chomp;

$lastSchema = $1 if /SET search_path = (\w+)/;

if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN
KEY|UNIQUE)\s*\((\w+)\)/){ 

    my $key = "$lastSchema.$table,$constraintType.$constraintName";

    my $aref = $tuplas{$key};
    unless ($aref) {

        $aref = [];
        $tuplas{$key} = $aref;

    }

    push @$aref, $constName;

}

}

Bye
Hans

Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile