1 |
#!/usr/bin/perl -w |
#!/usr/bin/perl -w |
2 |
|
|
3 |
# Helper script to produce alter tables for inherited tables and |
# Helper script to produce alter tables for inherited tables and |
4 |
# indexes from source shema |
# indexes from source shema like this: |
5 |
|
# |
6 |
|
# ./mkindex schema.sql | psql database_name |
7 |
|
|
8 |
use strict; |
use strict; |
9 |
#use Data::Dumper; |
use Data::Dumper; |
10 |
|
|
11 |
my $out; |
my $out; |
12 |
|
|
13 |
my ($table, $inherit); |
my ($table, $inherit); |
14 |
|
|
15 |
|
print "begin;\n"; |
16 |
|
|
17 |
while (<>) { |
while (<>) { |
18 |
chomp; |
chomp; |
19 |
|
|
34 |
$out->{inherits}->{$table} = $1; |
$out->{inherits}->{$table} = $1; |
35 |
} |
} |
36 |
|
|
37 |
|
if (s/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/$1$2/i) { |
38 |
|
@{ $out->{references}->{$table}->{$1} } = ( $3, $4 ); |
39 |
|
} |
40 |
|
|
41 |
|
print "$_\n"; |
42 |
|
print STDERR "# $_\n"; |
43 |
|
|
44 |
} |
} |
45 |
|
|
46 |
#print STDERR Dumper($out); |
print STDERR Dumper($out); |
47 |
|
|
48 |
foreach my $table (keys %{ $out->{inherits} }) { |
foreach my $table (keys %{ $out->{inherits} }) { |
49 |
my $parent = $out->{inherits}->{$table} || die; |
my $parent = $out->{inherits}->{$table} || die; |
60 |
} |
} |
61 |
} |
} |
62 |
|
|
63 |
|
foreach my $table (keys %{ $out->{references} }) { |
64 |
|
foreach my $field (keys %{ $out->{references}->{$table} }) { |
65 |
|
my $fk = $out->{references}->{$table}->{$field} || die; |
66 |
|
my $func = $table . '_' . $field . '_fkey'; |
67 |
|
print qq{ |
68 |
|
create or replace function $func() returns TRIGGER AS |
69 |
|
\$\$ |
70 |
|
DECLARE |
71 |
|
BEGIN |
72 |
|
IF NEW.$field IN (select $fk->[1] from $fk->[0]) THEN |
73 |
|
RETURN NEW; |
74 |
|
ELSE |
75 |
|
RAISE EXCEPTION 'insert or update on table "%" violates foreign key constraint for "$table" table', TG_RELNAME; |
76 |
|
END IF; |
77 |
|
END; |
78 |
|
\$\$ language 'plpgsql'; |
79 |
|
}; |
80 |
|
} |
81 |
|
} |
82 |
|
|
83 |
|
print "commit;\n"; |