Postgres Global string search

gistfile1.txt

-- This function will do a global search through a Postgres database.

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
--       FROM information_schema.columns c
--         JOIN information_schema.tables t ON
--           (t.table_name=c.table_name AND t.table_schema=c.table_schema)
--         JOIN information_schema.table_privileges p ON
--           (t.table_name=p.table_name AND t.table_schema=p.table_schema
--               AND p.privilege_type='SELECT')
--         JOIN information_schema.schemata s ON
--           (s.schema_name=t.table_schema)
--         JOIN pg_stat_user_tables row_count ON row_count.schemaname = t.table_schema AND row_count.relname = t.table_name
--       WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
--         AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
--         AND t.table_type='BASE TABLE'
        FROM information_schema.columns c
        JOIN information_schema.tables t ON (t.table_name=c.table_name AND t.table_schema=c.table_schema)
        JOIN information_schema.table_privileges p ON (t.table_name=p.table_name AND t.table_schema=p.table_schema AND p.privilege_type='SELECT')
        JOIN information_schema.schemata s ON (s.schema_name=t.table_schema)
        JOIN pg_stat_user_tables row_count ON row_count.schemaname = t.table_schema AND row_count.relname = t.table_name
        WHERE t.table_type='BASE TABLE'
          -- OPTIONAL
          -- Only look at tables with X rows in them
          AND row_count.n_live_tup < 200
  LOOP
    FOR rowctid IN
      EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
      )
    LOOP
      -- uncomment next line to get some progress report
      -- RAISE NOTICE 'hit in %.%', schemaname, tablename;
      RETURN NEXT;
    END LOOP;
 END LOOP;
END;
$$ language plpgsql;