| DO $$ <<lookup_contents>> | |
| DECLARE | |
| _search VARCHAR := 'something'; —what are you looking for? | |
| _limit INTEGER := 1000000; | |
| _total_columns INTEGER := 0; | |
| _total_read INTEGER := 0; | |
| _mark INTEGER; | |
| _text_columns REFCURSOR; | |
| _text_column RECORD; | |
| _address_columns REFCURSOR; | |
| _address_column RECORD; | |
| BEGIN | |
| RAISE NOTICE 'Look up contents for %', _search; | |
| —For each column in the database | |
| OPEN _text_columns FOR | |
| SELECT | |
| t.table_schema | |
| , t.table_name | |
| , c.column_name | |
| , CONCAT('SELECT ', c.column_name, ' AS data_found FROM ', t.table_schema, '.', t.table_name, ' WHERE (UPPER(', c.column_name,') LIKE UPPER(''%', _search, '%'')) ORDER BY ', c.column_name,';') AS "selection_query" | |
| FROM | |
| information_schema.tables t | |
| INNER JOIN | |
| information_schema.columns c | |
| ON c.table_name = t.table_name | |
| AND c.table_schema = t.table_schema | |
| WHERE | |
| (t.table_schema = 'public') —schema | |
| AND (t.table_type = 'BASE TABLE') —tables | |
| AND ((c.character_maximum_length IS NOT NULL) AND (c.character_maximum_length > 1)) —more than 1 character | |
| AND (c.udt_name IN ('bpchar', 'char', 'varchar', 'text')) —only texts | |
| ORDER BY | |
| t.table_schema, t.table_name | |
| ; | |
| LOOP | |
| FETCH _text_columns INTO _text_column; | |
| EXIT WHEN NOT FOUND; | |
| IF _total_read >= _limit THEN | |
| RAISE NOTICE 'Reached the limit of % reads', _limit; | |
| EXIT; | |
| END IF; | |
| _total_columns := _total_columns + 1; | |
| SELECT INTO _mark MOD(_total_columns, 10); | |
| IF _mark = 0 THEN | |
| RAISE NOTICE 'Processed % columns so far…', _total_columns; | |
| END IF; | |
| —RAISE NOTICE 'Executing: %', _text_column.selection_query; | |
| OPEN _address_columns FOR EXECUTE _text_column.selection_query; | |
| LOOP | |
| FETCH _address_columns INTO _address_column; | |
| EXIT WHEN NOT FOUND; | |
| _total_read := _total_read + 1; | |
| RAISE NOTICE 'Found: %.%.% = %', _text_column.table_schema, _text_column.table_name, _text_column.column_name, SUBSTRING(_address_column.data_found, 1, 100); | |
| IF _total_read >= _limit THEN | |
| RAISE NOTICE 'Reached the limit of % reads', _limit; | |
| EXIT; | |
| END IF; | |
| END LOOP; | |
| CLOSE _address_columns; | |
| END LOOP; | |
| CLOSE _text_columns; | |
| RAISE NOTICE 'Concluding % columns reads between % records', _total_columns, _total_read; | |
| END | |
| lookup_contents $$; |
19/Jul/2020
Leave a Reply