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