PostgreSQL-Search_contents.sql

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