pfbABPb7

1SELECT column_name
2  FROM dynamic_table
3  WHERE column_value = '255.255.255.255';
4	
5create or replace function find_all_columns(tablename in text) 
6    return setof record as
7$func$
8    declare r record;
9    begin 
10        return select   a.attname as "Column",
11            pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
12            from
13            pg_catalog.pg_attribute a
14            where
15            a.attnum > 0
16            and not a.attisdropped
17            and a.attrelid = (  select c.oid from pg_catalog.pg_class c left join pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relname ~ '^('  ||   quote_ident(tablename)    ||   ')$' and pg_catalog.pg_table_is_visible(c.oid);
18        end loop;
19    end;
20$func$ language 'plpgsql';  
21
22create or replace function find_value_in_table(tablename text) 
23    returns setof record as 
24$func$
25    declare r record;
26        return select 
27    begin 
28
29        for r in (select find_all_columns(tablename)) loop
30            return select * from tablename t where t... = "255.255.255.255" /*  here column would be the value in the record: r.Column*/ 
31        end loop;
32    end;
33$func$ language 'plpgsql';
34
35create or replace function find_tables_name(_username text)
36  returns setof record as
37$func$
38declare
39   tbl text;
40begin
41   for tbl in 
42      select t.tablename from pg_tables t
43      where  t.tableowner = _username and t.schemaname = 'public'
44   loop
45      return quote_ident(tbl);
46   end loop;
47end;
48$func$ language 'plpgsql';
49
50create or replace function find_value(_username text, valuetofind text) 
51    returns setof record as 
52$func$
53    declare r record;
54    begin 
55        for r in (select find_tables_name(_username)) loop
56            return find_value_in_table( r.tablename );
57        end loop;
58    end;
59$func$ language 'plpgsql';
60	
61CREATE OR REPLACE FUNCTION find_columns(_owner text
62                                       ,_valuetofind text
63                                       ,_part bool = FALSE)
64  RETURNS TABLE (tbl text, col text, typ text) LANGUAGE plpgsql STRICT AS
65$func$
66DECLARE
67   _go         bool;
68   _search_row text := '%' || _search || '%';  -- Search row for part of string
69BEGIN
70   IF _part THEN   -- search col for part of string?
71      valuetofind := '%' || valuetofind || '%';
72   END IF;
73
74FOR tbl IN 
75   SELECT quote_ident(t.schemaname) || '.' || quote_ident(t.tablename)
76   FROM pg_tables t
77   WHERE  t.tableowner = _owner
78-- AND t.schemaname = 'public' -- uncomment to only search one schema
79LOOP
80   EXECUTE '
81   SELECT EXISTS (
82      SELECT 1 FROM ' || tbl || ' t WHERE t::text ~~ $1)' -- check whole row
83   INTO  _go
84   USING _search_row;
85
86   IF _go THEN
87      FOR col, typ IN 
88         SELECT quote_ident(a.attname) -- AS col
89               ,pg_catalog.format_type(a.atttypid, a.atttypmod) -- AS typ
90         FROM   pg_catalog.pg_attribute a
91         WHERE  a.attnum > 0
92         AND    NOT a.attisdropped
93         AND    a.attrelid = tbl::regclass
94      LOOP
95         EXECUTE '
96         SELECT EXISTS (
97            SELECT 1
98            FROM ' || tbl || ' WHERE ' || col || '::text ~~ $1)' -- check col
99         INTO   _go
100         USING  valuetofind;
101
102         IF _go THEN
103            RETURN NEXT;
104         END IF;
105      END LOOP;
106   END IF;
107
108END LOOP;
109END;
110$func$;
111COMMENT ON FUNCTION x.find_columns(text, text, boolean) IS 'Search all tables
112 owned by "_owner" user for a value "_search" (text representation).
113 Match full or partial (_part)';
114	
115SELECT * FROM find_columns('postgres', '255.255.255.255');
116SELECT * FROM find_columns('fadmin', '255.255.255.255', TRUE);
117	
118tbl       |     col     | typ
119-----------------+-------------+------
120 event.eventkat  | eventkat    | text
121 public.foo      | description | text
122 public.bar      | filter      | text