Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 $$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.