root/sql/split.prism.dump.to.sprocs.pl

Revision a27cd9b462a686609eff744d1e6553da5605090b, 2.2 kB (checked in by Depesz Lubaczewski <depesz@omniti.com>, 5 years ago)

prism procedures, and simple tool to split pg_dump -n prism -s output into many files

  • Property mode set to 100755
Line 
1 #!/usr/bin/perl -w
2 use strict;
3 use Carp;
4 use autodie;
5
6 =head1 Running
7
8 To run this program simply call it like this:
9
10     pg_dump -s -n prism -U .... -h ... -p ... database_name | ./split.prism.dump.to.sprocs.pl
11
12 And that's all. If there will be problems - it will print them, and stop processing output.
13
14 =head1 Potential problems
15
16 If you'll get error like:
17
18     Can't locate autodie.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.8.8 /usr/local/share/perl/5.8.8 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl .) at ./split.prism.dump.to.sprocs.pl line 4.
19     BEGIN failed--compilation aborted at ./split.prism.dump.to.sprocs.pl line 4.
20
21 Change 4th line of this script from:
22
23     use autodie;
24
25 to:
26
27     use Fatal qw( open );
28
29 or install autodie Perl module.
30
31 =cut
32
33 my %buffers = ();
34
35 my $content = do { local $/; <STDIN> };
36
37 for my $function ( $content =~ m{ ^ ( CREATE \s+ FUNCTION \s+ .*? \s+ LANGUAGE \s+ [^;]* ; [^\S\n]* \n ) }xmsg ) {
38
39     croak "Can't find function name in:\n$function\n" unless
40         $function =~ s/\ACREATE FUNCTION ([^\s\(]+)/CREATE OR REPLACE FUNCTION prism.$1/;
41
42     my $function_name = $1;
43     push @{ $buffers{ $function_name } }, $function;
44 }
45
46 for my $alter_statement ( $content =~ m{ ^ ( ALTER \s+ FUNCTION \s+ prism [^\n]* \n ) }xmsg ) {
47     croak "Can't find function name in:\n$alter_statement\n" unless
48         $alter_statement =~ m{^ALTER \s+ FUNCTION \s+ prism \. ( [^\s\(]+ )}xms;
49
50     my $function_name = $1;
51     push @{ $buffers{ $function_name } }, $alter_statement;
52 }
53
54 for my $privileges_statement ( $content =~ m{ ^ ( (?: GRANT | REVOKE ) \s+ [^\n]+ \s+ ON \s+ FUNCTION \s+ [^\n]* \n ) }xmsg ) {
55     croak "Can't find function name in:\n$privileges_statement\n" unless
56         $privileges_statement =~ m{^(?: GRANT | REVOKE ) \s+ [^\n]+ \s+ ON \s+ FUNCTION \s+ ( [^\s\(]+ )}xms;
57
58     my $function_name = $1;
59     push @{ $buffers{ $function_name } }, $privileges_statement;
60 }
61
62 while ( my ( $function_name, $buffer ) = each %buffers ) {
63
64     my $fullname = "sprocs/prism.$function_name.sql";
65     $fullname =~ s/"//g;    # remove quoting of function names
66
67     open my $fh, '>', $fullname;
68     print $fh join( "\n\n", @{ $buffer } );
69     close $fh;
70 }
71
72 exit;
Note: See TracBrowser for help on using the browser.