2

In the function find_value_in_table() provided below, I am trying to find the name of any columns which have a record where the column value matches the String "255.255.255.255".

In short, something like this:

SELECT column_name FROM dynamic_table WHERE column_value = '255.255.255.255';

Note:

  • the name of the table is passed in as a parameter to the function, hence the "dynamic_table"
  • I am trying to determine the name of the column, not the data value.

This is a first step. Later I will parameterize the column_value as well. I know there is a table storing the value "255.255.255.255" somewhere, and want to prove functionality of this function by finding the table and column name storing this value.

The aim of all this: I have big database, that I am retro-engineering. I know it contains, somewhere, some configuration parameters of an application. In the current configuration I know the precise value of some of these parameters (seen in the application config GUI: e.g. a computer name, ip addresses). I want to browse the entire database in order to determine which tables store this configuration information.

I have been building the function find_value() to return these clues.

How can this be done?

create or replace function find_all_columns(tablename in text) return setof record as $func$ declare r record; begin return select a.attname as "Column", pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype" from pg_catalog.pg_attribute a where a.attnum > 0 and not a.attisdropped 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); end loop; end; $func$ language 'plpgsql'; create or replace function find_value_in_table(tablename text) returns setof record as $func$ declare r record; return select begin for r in (select find_all_columns(tablename)) loop return select * from tablename t where t... = "255.255.255.255" /* here column would be the value in the record: r.Column*/ end loop; end; $func$ language 'plpgsql'; create or replace function find_tables_name(_username text) returns setof record as $func$ declare tbl text; begin for tbl in select t.tablename from pg_tables t where t.tableowner = _username and t.schemaname = 'public' loop return quote_ident(tbl); end loop; end; $func$ language 'plpgsql'; create or replace function find_value(_username text, valuetofind text) returns setof record as $func$ declare r record; begin for r in (select find_tables_name(_username)) loop return find_value_in_table( r.tablename ); end loop; end; $func$ language 'plpgsql';
Improve this question
1

1 Answer 1

Reset to default
3

One primitive way to achieve this would be to make a plain-text dump and use an editor of your choice (vim in my case) to search for the string.

But this function does a better job. :)

CREATE OR REPLACE FUNCTION find_columns(_owner text ,_valuetofind text ,_part bool = FALSE) RETURNS TABLE (tbl text, col text, typ text) LANGUAGE plpgsql STRICT AS $func$ DECLARE _go bool; _search_row text := '%' || _search || '%'; -- Search row for part of string BEGIN IF _part THEN -- search col for part of string? valuetofind := '%' || valuetofind || '%'; END IF; FOR tbl IN SELECT quote_ident(t.schemaname) || '.' || quote_ident(t.tablename) FROM pg_tables t WHERE t.tableowner = _owner -- AND t.schemaname = 'public' -- uncomment to only search one schema LOOP EXECUTE ' SELECT EXISTS ( SELECT 1 FROM ' || tbl || ' t WHERE t::text ~~ $1)' -- check whole row INTO _go USING _search_row; IF _go THEN FOR col, typ IN SELECT quote_ident(a.attname) -- AS col ,pg_catalog.format_type(a.atttypid, a.atttypmod) -- AS typ FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = tbl::regclass LOOP EXECUTE ' SELECT EXISTS ( SELECT 1 FROM ' || tbl || ' WHERE ' || col || '::text ~~ $1)' -- check col INTO _go USING valuetofind; IF _go THEN RETURN NEXT; END IF; END LOOP; END IF; END LOOP; END; $func$; COMMENT ON FUNCTION x.find_columns(text, text, boolean) IS 'Search all tables owned by "_owner" user for a value "_search" (text representation). Match full or partial (_part)';

Call:

SELECT * FROM find_columns('postgres', '255.255.255.255'); SELECT * FROM find_columns('fadmin', '255.255.255.255', TRUE);

Returns:

 tbl | col | typ -----------------+-------------+------ event.eventkat | eventkat | text public.foo | description | text public.bar | filter | text

Tested with PostgreSQL 9.1

Major points

  • The function is a one-stop-shop.

  • I built an option to search for part of the value (_part). The default is to search for whole columns.

  • I built in a quick test on the whole row to eliminate tables, that don't have the valuetofind in them at all. I use PostgreSQL's ability to convert whole rows to text quickly for this. This should make the function a lot faster - except when all or almost all tables qualify or when tables only have one columns.

  • I define the return type as RETURNS TABLE (tbl text, col text, typ text) and assign the implicitly defined variables tbl, col and typ right away. So I don't need additional variables and can RETURN NEXT right away when a column qualifies.

  • Make heavy use of EXISTS here! That's the fastest option, as you are only interested whether the column has the value at all.

  • Use LIKE (or ~~ for short) instead of regular expressions. Simpler, faster.

  • I quote_ident() all identifiers right away.

  • EXECUTE *command* INTO USING is instrumental.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.