19/Jul/2020

PostgreSQL script to look up for a text

By Gabriel

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

view raw PostgreSQL-Search_contents.sql hosted with ❤ by GitHub

This entry was posted on Sunday, July 19th, 2020 at 7:41 pm and posted in Tips. You can follow any responses to this entry through the RSS 2.0 feed.

Leave a Reply Cancel reply

Enter your comment here...

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

Email (required) (Address never made public)

Name (required)

Website

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

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

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

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

Connecting to %s