I have a database dumped to sql with create table
commands and I need to create a script to apply a given command to some of the objects, such as dropping the table as an example.
In the case of dropping the tables for instance I scan for lines starting with create table
and extract the 3rd field with the cut
command. After that I want to prefix each output line with a command such as drop table
followed by cascade;
or another suitable commands.
The command I use now is cat datadump.sql | cut -d ' ' -f 1-3 | grep 'CREATE TABLE' | uniq | cut -d ' ' -f 3
.
This outputs a list like
table1
table2
table3
which I want to convert to
drop table table1 cascade;
drop table table2 cascade;
etc. and save it a file to be run when needed. In fact I'd like to make the prefix and suffix conditional on the input but for now simple literal text should suffice.
Here is some sample input as requested in the comments:
CREATE TABLE agency (
agrecordid integer DEFAULT nextval(('agency0_pk_seq'::text)::regclass) NOT NULL,
agidcode1 character(20) NOT NULL,
...,
...,
agaddress1 character(40),
agtown character(40),
agfax2 character(20),
agemail character(20)
);
CREATE TABLE agency_contact (
recordid integer DEFAULT nextval(('agencon0_pk_seq'::text)::regclass) NOT NULL,
agencyid integer,
name character varying(35),
...,
...,
email2 character varying(30),
deptorperson character(1),
notes text
);
awk '/create table/ {printf "%s\n" "drop table " $3 " cascade;"}' file > newfile
– jasonwryan May 11 '15 at 0:29create table
and prints the third field of any matches, with the other stuff around it... – jasonwryan May 11 '15 at 0:35awk '/CREATE +TABLE/ { printf ("drop table %s cascade;\n", $3); }' datadump.sql
– Paul_Pedant Dec 9 '19 at 12:49