how to search entire database on postgresql ?

select * from search_columns(‘%OTE5MWY4NzEzIn0=%’);

-- Function: search_columns(text, name[], name[])

-- DROP FUNCTION search_columns(text, name[], name[]);

CREATE OR REPLACE FUNCTION search_columns(
IN needle text,
IN haystack_tables name[] DEFAULT '{}'::name[],
IN haystack_schema name[] DEFAULT '{public}'::name[])
RETURNS TABLE(schemaname text, tablename text, columnname text, rowctid text) AS
$BODY$
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)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND c.table_schema=ANY(haystack_schema)
AND t.table_type='BASE TABLE'
LOOP
/* EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', */
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) LIKE %L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION search_columns(text, name[], name[])
OWNER TO weakapi;

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s