How to determine if text phrase exists in a table column
Common Case Scenario:

You have a very aggravated person who demands you purge their email from any table you have in your system. You have lots of tables that have email addresses. How do you find which tables have this person's email address.

Below is a handy plpgsql function we wrote that does the following. Given a search criteria, field name pattern, table_name pattern, schema name pattern, data type pattern, and max length of field to check, it will search all fields in the database fitting those patterns and return to you the names of these schema.table.field names that contain the search phrase.

To use the below you would do something like:
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

Code of the function looks like this:


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;