Cacher is the code snippet organizer for pro developers

-- SELECT pc_search_tablefield('%Charter has been activated%', '%', '%', '%', '%', null); 
-- The above returns all column names  tht contains given text
-- SELECT pc_search_tablefield('%john@hotmail%', '%email%', '%', '%', '%', null); 
-- The above will return all database field names that have the phrase email in the field name and that contain the term john@hotmail
CREATE OR REPLACE FUNCTION pc_search_tablefield(param_search text, param_field_like text, param_table_like text, 
    param_schema_like text, param_datatype_like text, param_max_length integer)
  RETURNS text AS
$$
DECLARE
    result text := '';
    var_match text := '';
    searchsql text := '';
BEGIN
    searchsql := array_to_string(ARRAY(SELECT 'SELECT ' || quote_literal(quote_ident(c.table_schema) || '.' 
        || quote_ident(c.table_name) || '.' || quote_ident(c.column_name)) ||
                ' WHERE EXISTS(SELECT ' || quote_ident(c.column_name) || ' FROM ' 
                 || quote_ident(c.table_schema) || '.' || quote_ident(c.table_name) ||
                 ' WHERE ' || CASE WHEN c.data_type IN('character', 'character varying', 'text') THEN 
                    quote_ident(c.column_name) ELSE 'CAST(' || quote_ident(c.column_name) || ' As text) ' END 
                    || ' LIKE ' || quote_literal(param_search) || ') ' As subsql
                FROM information_schema.columns c
                WHERE c.table_schema NOT IN('pg_catalog', 'information_schema') 
                    AND c.table_name LIKE param_table_like 
                    AND c.table_schema LIKE param_schema_like 
                    AND c.column_name LIKE param_field_like
                    AND c.data_type IN('"char"','character', 'character varying', 'integer', 'numeric', 'real', 'text')
                        AND c.data_type LIKE param_datatype_like
                    AND (param_max_length IS NULL OR param_max_length = 0 
                        OR character_maximum_length <= param_max_length) AND 
                        EXISTS(SELECT t.table_name 
                            FROM information_schema.tables t 
                            WHERE t.table_type = 'BASE TABLE' 
                                AND t.table_name = c.table_name AND t.table_schema = c.table_schema)),
                    ' UNION ALL ' || E'\r');
    --do an exists check thru all tables/fields that match field table pattern
    --return those schema.table.fields that contain search pattern information
    RAISE NOTICE '%', searchsql;
    IF searchsql > '' THEN
        FOR var_match IN EXECUTE(searchsql) LOOP 
            IF result > '' THEN
                result := result || ';' || var_match;
            ELSE
                result := var_match;
            END IF;
        END LOOP;
    END IF;         
    RETURN result;
END;$$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;