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 );