This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 $$; |
Advertisement
Leave a Reply