search_in_tables

search_in_tables.sql

/*
 * search_in_tables
 *
 * Usage example:
 *     SELECT * FROM search_in_tables('public', 'foo', '%pattern%');
 *
 */
CREATE OR REPLACE FUNCTION search_in_tables(                                                                                                                                                                                                                                                                                                                               
  IN  ptable_schema   TEXT,                                                                                                                                                                                                                                                                                                                                                
  IN  ptable_name     TEXT,                                                                                                                                                                                                                                                                                                                                                
  IN  psearch_pattern TEXT,                                                                                                                                                                                                                                                                                                                                                
  OUT schema_found   TEXT,                                                                                                                                                                                                                                                                                                                                                 
  OUT table_found    TEXT,                                                                                                                                                                                                                                                                                                                                                 
  OUT column_found   TEXT,                                                                                                                                                                                                                                                                                                                                                 
  OUT where_expr     TEXT                                                                                                                                                                                                                                                                                                                                                  
) RETURNS setof record AS                                                                                                                                                                                                                                                                                                                                                  
$$                                                                                                                                                                                                                                                                                                                                                                         
DECLARE                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                           
  rCatalog RECORD;                                                                                                                                                                                                                                                                                                                                                         
  sQuery   TEXT DEFAULT '';                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                           
  lFound   BOOLEAN DEFAULT false;                                                                                                                                                                                                                                                                                                                                          
BEGIN                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                           
  FOR rCatalog IN                                                                                                                                                                                                                                                                                                                                                          
    SELECT a.table_schema,                                                                                                                                                                                                                                                                                                                                                 
           a.table_name,                                                                                                                                                                                                                                                                                                                                                   
           a.column_name                                                                                                                                                                                                                                                                                                                                                   
      FROM information_schema.columns a                                                                                                                                                                                                                                                                                                                                    
     WHERE a.table_schema like coalesce(ptable_schema, 'public')                                                                                                                                                                                                                                                                                                           
       AND a.table_name   like ptable_name                                                                                                                                                                                                                                                                                                                                 
       AND a.udt_name in ('text', 'bpchar', 'varchar')                                                                                                                                                                                                                                                                                                                     
       AND a.table_schema NOT IN ('pg_catalog', 'pg_toast', 'information_schema')                                                                                                                                                                                                                                                                                          
       AND a.table_schema !~ '^pg_'                                                                                                                                                                                                                                                                                                                                        
     ORDER BY a.table_schema, a.table_name, a.ordinal_position                                                                                                                                                                                                                                                                                                             
  LOOP                                                                                                                                                                                                                                                                                                                                                                     
    schema_found := quote_ident(rCatalog.table_schema);                                                                                                                                                                                                                                                                                                                    
    table_found  := quote_ident(rCatalog.table_name);                                                                                                                                                                                                                                                                                                                      
    column_found := quote_ident(rCatalog.column_name);                                                                                                                                                                                                                                                                                                                     
    where_expr   := table_found||'.'||column_found||' ilike '||quote_literal(psearch_pattern);                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                           
    sQuery := 'SELECT EXISTS(SELECT 1 FROM '||quote_ident(rCatalog.table_schema)||'.'||quote_ident(rCatalog.table_name);                                                                                                                                                                                                                                                   
    sQuery := sQuery || ' WHERE '||quote_ident(rCatalog.column_name)||' ilike '||quote_literal(psearch_pattern)||')';                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                           
    RAISE INFO 'Searching pattern % in %.%.%',                                                                                                                                                                                                                                                                                                                             
      quote_literal(psearch_pattern),                                                                                                                                                                                                                                                                                                                                      
      schema_found,                                                                                                                                                                                                                                                                                                                                                        
      table_found,                                                                                                                                                                                                                                                                                                                                                         
      column_found;                                                                                                                                                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                                                                                           
    EXECUTE sQuery INTO lFound;                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                           
    IF lFound IS TRUE THEN                                                                                                                                                                                                                                                                                                                                                 
      RETURN NEXT;                                                                                                                                                                                                                                                                                                                                                         
    END IF;                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                           
  END LOOP;                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                           
  RETURN;                                                                                                                                                                                                                                                                                                                                                                  
END;                                                                                                                                                                                                                                                                                                                                                                       
$$                                                                                                                                                                                                                                                                                                                                                                         
LANGUAGE plpgsql;