3 # Helper script to produce alter tables for inherited tables and
4 # indexes from source shema like this:
6 # ./mkindex schema.sql | psql database_name
13 my ($table, $inherit);
20 if (/create\s+table\s+(\S+)/i) {
26 if (/primary\s+key\s*\(\s*(\S+)\s*\)/i ) {
27 $out->{table_pk}->{$table} = $1;
29 if (/^\s*(\S+)\s*.+?--\s*((?:unique\s+)*index)/i) {
30 $out->{index}->{$2}->{$table} = $1;
33 if (/\s*inherits\s*\(\s*(\S+)\s*\)/i) {
34 $out->{inherits}->{$table} = $1;
37 if (s/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)([^,]*)([,\s]*)$/\t$1$2$6/i) {
38 # if (/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/) {
39 @{ $out->{references}->{$table}->{$1} } = ( $3, $4, $5 );
43 print STDERR "# $_\n";
47 print STDERR Dumper($out);
49 foreach my $table (keys %{ $out->{inherits} }) {
50 my $parent = $out->{inherits}->{$table} || die "$table doesn't inherit anything";
51 my $pk = $out->{table_pk}->{$parent} || die "$parent doesn't have primary key";
52 my $seq = $parent . '_' . $pk . '_seq';
53 print qq{alter table $table alter column $pk set default nextval('$seq');\n};
56 foreach my $type (keys %{ $out->{index} }) {
57 foreach my $table (keys %{ $out->{index}->{$type} }) {
58 my $f = $out->{index}->{$type}->{$table} || die;
59 my $i = $table . '_' . $f . '_ind';
60 print qq{create $type $i on $table($f);\n};
64 foreach my $table (keys %{ $out->{references} }) {
65 foreach my $field (keys %{ $out->{references}->{$table} }) {
66 my $fk = $out->{references}->{$table}->{$field} || die;
67 my $func = $table . '_' . $field . '_fkey';
69 create or replace function $func() returns TRIGGER AS
73 IF NEW.$field IN (select $fk->[1] from $fk->[0]) THEN
76 RAISE EXCEPTION 'insert or update on table "%" violates foreign key constraint for "$table" table', TG_RELNAME;
79 \$\$ language 'plpgsql';
80 CREATE TRIGGER $func BEFORE INSERT ON $table FOR EACH ROW EXECUTE PROCEDURE $func();