Capturing group regex with grep-CodePudding

I'm trying to capture SQL DDL "CREATE" from a PostgreSQL schema dump that looks like this:

SET default_table_access_method = heap;

CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);

ALTER TABLE schema_name.table_name OWNER TO user;

CREATE INDEX index ON schema_name.table_name USING btree (col1);

What I want is:

CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);`

Why grep -Po "(CREATE TABLE)[\S\s]*(;)" dump.sql is not working?

In PCRE2 /CREATE TABLE [\w]*\.[\w]*[\S\s]*(;)/U matches properly.

thanks.

CodePudding user response：

sed would be better tool for this:

sed -n '/^CREATE TABLE/,/;$/p' file.sql

CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);

If you really want a gnu-grep solution then use:

grep -zPo "(?m)^CREATE TABLE[^;] ;\R" file.sql

CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);

CodePudding user response：

Unsure about your regex, but this works:

grep -Poz "CREATE TABLE[^;]*;" dump.sql

Gives:

CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);

CodePudding user response：

Since this is tagged perl... here's a quick script using an old but nifty module I found, SQL::Script, to parse the SQL dump:

#!/usr/bin/env perl
use strict;
use warnings;
use feature qw/say/;
use SQL::Script; # Install with your favorite CPAN client

# Pass the dump file name as the command-line argument

my $script = SQL::Script->new;
$script->read($ARGV[0]);
foreach my $stmt ($script->statements) {
    say "$stmt;" if $stmt =~ /^CREATE TABLE/i;
}

Example:

$ ./dump_tables test.sql
CREATE TABLE schema_name.table_name (
    col1 bigint,
    col2 text
);

CodePudding user response：

With GNU awk you could try following awk program.

awk -v RS='\nCREATE[^)]*\n\\);' 'RT{gsub(/(^|$)\n/,"",RT);print RT}' Input_file

Explanation: Using GNU awk, setting RS variable of awk to \nCREATE[^)]*\n\\); to get only required part in shown samples. Then in main program checking condition if RT is NOT NULL then removing starting and ending lines from it and printing it, to get only needed part out of sql's output.