PostgreSQL script to look up for a text

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 $$;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s