· 7 years ago · Nov 03, 2018, 06:58 AM
1$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
2$ grep United a.tmp
3INSERT INTO countries VALUES ('US', 'United States');
4INSERT INTO countries VALUES ('GB', 'United Kingdom');
5
6$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
7$ grep country_code a.tmp
8INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United States');
9INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');
10
11CREATE OR REPLACE FUNCTION search_columns(
12 needle text,
13 haystack_tables name[] default '{}',
14 haystack_schema name[] default '{}'
15)
16RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
17AS $$
18begin
19 FOR schemaname,tablename,columnname IN
20 SELECT c.table_schema,c.table_name,c.column_name
21 FROM information_schema.columns c
22 JOIN information_schema.tables t ON
23 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
24 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
25 AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
26 AND t.table_type='BASE TABLE'
27 LOOP
28 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
29 schemaname,
30 tablename,
31 columnname,
32 needle
33 ) INTO rowctid;
34 IF rowctid is not null THEN
35 RETURN NEXT;
36 END IF;
37 END LOOP;
38END;
39$$ language plpgsql;
40
41SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L
42
43SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L
44
45SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L
46
47SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L
48
49SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L
50
51SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %L
52
53CREATE OR REPLACE FUNCTION search_columns(
54 needle text,
55 haystack_columns name[] default '{}',
56 haystack_tables name[] default '{}',
57 haystack_schema name[] default '{public}'
58)
59RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
60AS $$
61begin
62 FOR schemaname,tablename,columnname IN
63 SELECT c.table_schema,c.table_name,c.column_name
64 FROM information_schema.columns c
65 JOIN information_schema.tables t ON
66 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
67 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
68 AND c.table_schema=ANY(haystack_schema)
69 AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}')
70 AND t.table_type='BASE TABLE'
71 LOOP
72 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
73 schemaname,
74 tablename,
75 columnname,
76 needle
77 ) INTO rowctid;
78 IF rowctid is not null THEN
79 RETURN NEXT;
80 END IF;
81 END LOOP;
82END;
83$$ language plpgsql;
84
85SELECT * FROM search_columns('86192700'
86 , array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a
87 INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
88 WHERE
89 a.column_name iLIKE '%cep%'
90 AND b.table_type = 'BASE TABLE'
91 AND b.table_schema = 'public'
92 )
93
94 , array(SELECT b.table_name::name FROM information_schema.columns AS a
95 INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
96 WHERE
97 a.column_name iLIKE '%cep%'
98 AND b.table_type = 'BASE TABLE'
99 AND b.table_schema = 'public')
100);
101
102DO $$
103DECLARE
104 value int := 0;
105 sql text := 'The constructed select statement';
106 rec1 record;
107 rec2 record;
108BEGIN
109 DROP TABLE IF EXISTS _x;
110 CREATE TEMPORARY TABLE _x (
111 schema_name text,
112 table_name text,
113 column_name text,
114 found text
115 );
116 FOR rec1 IN
117 SELECT table_schema, table_name, column_name
118 FROM information_schema.columns
119 WHERE table_name <> '_x'
120 AND UPPER(column_name) LIKE UPPER('%%')
121 AND table_schema <> 'pg_catalog'
122 AND table_schema <> 'information_schema'
123 AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
124 LOOP
125 sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%my_substring_to_find_goes_here%' , ''')');
126 RAISE NOTICE '%', sql;
127 BEGIN
128 FOR rec2 IN EXECUTE sql LOOP
129 RAISE NOTICE '%', sql;
130 INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
131 END LOOP;
132 EXCEPTION WHEN OTHERS THEN
133 END;
134 END LOOP;
135 END; $$;
136
137SELECT * FROM _x;
138
139CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
140 RETURNS TABLE(_tbl regclass, _ctid tid) AS
141$func$
142BEGIN
143 FOR _tbl IN
144 SELECT c.oid::regclass
145 FROM pg_class c
146 JOIN pg_namespace n ON n.oid = relnamespace
147 WHERE c.relkind = 'r' -- only tables
148 AND n.nspname !~ '^(pg_|information_schema)' -- exclude system schemas
149 ORDER BY n.nspname, c.relname
150 LOOP
151 RETURN QUERY EXECUTE format(
152 'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
153 , _tbl, '%' || _like_pattern || '%')
154 USING _tbl;
155 END LOOP;
156END
157$func$ LANGUAGE plpgsql;
158
159SELECT * FROM search_whole_db('mypattern');
160
161CREATE OR REPLACE FUNCTION search_columns(
162 needle text,
163 haystack_tables name[] default '{}',
164 haystack_schema name[] default '{public}',
165 progress_seq text default NULL
166)
167RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
168AS $$
169DECLARE
170currenttable text;
171columnscount integer;
172foundintables text[];
173foundincolumns text[];
174begin
175currenttable='';
176columnscount = (SELECT count(1)
177 FROM information_schema.columns c
178 JOIN information_schema.tables t ON
179 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
180 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
181 AND c.table_schema=ANY(haystack_schema)
182 AND t.table_type='BASE TABLE')::integer;
183PERFORM setval(progress_seq::regclass, columnscount);
184
185 FOR schemaname,tablename,columnname IN
186 SELECT c.table_schema,c.table_name,c.column_name
187 FROM information_schema.columns c
188 JOIN information_schema.tables t ON
189 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
190 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
191 AND c.table_schema=ANY(haystack_schema)
192 AND t.table_type='BASE TABLE'
193 LOOP
194 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
195 schemaname,
196 tablename,
197 columnname,
198 needle
199 ) INTO rowctid;
200 IF rowctid is not null THEN
201 RETURN NEXT;
202 foundintables = foundintables || tablename;
203 foundincolumns = foundincolumns || columnname;
204 RAISE NOTICE 'FOUND! %, %, %, %', schemaname,tablename,columnname, rowctid;
205 END IF;
206 IF (progress_seq IS NOT NULL) THEN
207 PERFORM nextval(progress_seq::regclass);
208 END IF;
209 IF(currenttable<>tablename) THEN
210 currenttable=tablename;
211 IF (progress_seq IS NOT NULL) THEN
212 RAISE NOTICE 'Columns left to look in: %; looking in table: %', currval(progress_seq::regclass), tablename;
213 EXECUTE 'COPY (SELECT unnest(string_to_array(''Current table (column ' || columnscount-currval(progress_seq::regclass) || ' of ' || columnscount || '): ' || tablename || 'nnFound in tables/columns:n' || COALESCE(
214 (SELECT string_agg(c1 || '/' || c2, 'n') FROM (SELECT unnest(foundintables) AS c1,unnest(foundincolumns) AS c2) AS t1)
215 , '') || ''',''n''))) TO ''c:WINDOWStemp' || progress_seq || '.txt''';
216 END IF;
217 END IF;
218 END LOOP;
219END;
220$$ language plpgsql;