用于动态查找列名的函数

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".

在下面提供的函数find_value_in_table()中,我试图找到具有记录的列的名称,其中列值与字符串“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"

表的名称作为参数传递给函数,因此“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.

这是第一步。稍后我也会参数化column_value。我知道有一个表存储值“255.255.255.255”的某个地方,并希望通过查找存储该值的表和列名来证明此函数的功能。

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.

所有这一切的目标:我有大数据库,我是复古工程。我知道它包含某个应用程序的一些配置参数。在当前配置中,我知道其中一些参数的精确值(在应用程序配置GUI中可见:例如计算机名称,IP地址)。我想浏览整个数据库,以确定哪些表存储此配置信息。

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

我一直在构建函数find_value()来返回这些线索。

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';

1 个解决方案

#1

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.

实现这一目标的一种原始方法是进行纯文本转储并使用您选择的编辑器(在我的情况下为vim)来搜索字符串。

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

使用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.

  我构建了一个选项来搜索部分值(_part)。默认设置是搜索整列。

• 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.

  我在整行上构建了一个快速测试来消除表格,这些表格根本没有其中的值。我使用PostgreSQL的能力将整行快速转换为文本。这应该使函数更快 - 除非所有或几乎所有表都符合条件或表只有一列。

• 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.

  我将返回类型定义为RETURNS TABLE(tbl text,col text,typ text),并立即分配隐式定义的变量tbl,col和typ。所以我不需要额外的变量,并且当列合格时可以立即返回NEXT。

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

  在这里大量使用EXISTS!这是最快的选择,因为您只关心该列是否具有该值。

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

  使用LIKE(或简称~~)代替正则表达式。更简单,更快捷。

• I quote_ident() all identifiers right away.

  我是quote_ident()所有标识符。

• EXECUTE *command* INTO USING is instrumental.

  EXECUTE *命令* INTO USING是有用的。