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 engineer databases, 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 duplicated definitions, 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