Postgres在给定列上找到符合条件的数据库表中的所有行 [英] Postgres find all rows in database tables matching criteria on a given column

问题描述

我试图编写子查询，以便我在所有表中搜索名为id的列，并且由于存在多个具有id列的表，因此我想添加条件，以使id = 3119093. /p>

我的尝试是:

Select * 
from information_schema.tables 
where id = '3119093' and id IN (
    Select table_name 
    from information_schema.columns 
    where column_name = 'id' );

这没有用，所以我尝试了:

Select * 
from information_schema.tables 
where table_name IN (
    Select table_name 
    from information_schema.columns 
    where column_name = 'id' and 'id' IN (
        Select * from table_name where 'id' = 3119093));

这也不是正确的方法.任何帮助，将不胜感激.谢谢！

更难的尝试是:

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE'
        --AND c.column_name = "id"
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) like %L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

select * from search_columns('%3119093%'::varchar,'{}'::name[]) ;

唯一的问题是此代码显示表名和列名.然后，我必须手动输入

Select * from table_name where id = 3119093

我从上面的代码中获得了表名.

我想自动实现从表返回的行，但是我不知道如何自动获取表名.

解决方案

我花了一些时间为您服务.

对于初学者来说，有关代码内部发生了什么的一些信息.

说明

1. 函数接受两个输入参数:列名和列值
2. 它需要一个创建的类型，它将返回一组
3. 第一个循环标识具有指定列名作为输入参数的表
4. 然后，它形成一个查询，该查询汇总从步骤3提取的每个表中与输入条件匹配的所有行，并基于ILIKE进行比较-如您的示例
5. 仅当当前访问的表中至少有一行符合指定条件(然后数组不为空)时，函数才会进入第二个循环
6. 第二个循环取消嵌套符合条件的行数组，并使用RETURN NEXT rec子句将每个元素放入函数输出

注释

• 使用LIKE搜索效率低下-我建议添加另一个输入参数列类型"，并通过向pg_catalog.pg_type表添加联接来限制其在查找中的作用.

• 第二个循环在那里，因此，如果为特定表发现多于1行，则将返回每一行.

• 如果您正在寻找其他东西，例如需要键值对，而不仅是值，那么就需要扩展功能.例如，您可以从行中构建json格式.

---

现在，输入代码.

测试用例

CREATE TABLE tbl1 (col1 int, id int); -- does contain values
CREATE TABLE tbl2 (col1 int, col2 int); -- doesn't contain column "id"
CREATE TABLE tbl3 (id int, col5 int); -- doesn't contain values

INSERT INTO tbl1 (col1, id)
  VALUES (1, 5), (1, 33), (1, 25);

表存储数据:

postgres=# select * From tbl1;

 col1 | id
------+----
    1 |  5
    1 | 33
    1 | 25
(3 rows)

创建类型

CREATE TYPE sometype AS ( schemaname text, tablename text, colname text, entirerow text );

功能代码

CREATE OR REPLACE FUNCTION search_tables_for_column (
    v_column_name text
  , v_column_value text
)
RETURNS SETOF sometype
LANGUAGE plpgsql
STABLE
AS
$$
DECLARE
  rec           sometype%rowtype;
  v_row_array   text[];
  rec2          record;
  arr_el        text;
BEGIN
FOR rec IN
  SELECT 
      nam.nspname AS schemaname
    , cls.relname AS tablename
    , att.attname AS colname
    , null::text AS entirerow
  FROM 
    pg_attribute att
    JOIN pg_class cls ON att.attrelid = cls.oid 
    JOIN pg_namespace nam ON cls.relnamespace = nam.oid 
  WHERE 
    cls.relkind = 'r'
    AND att.attname = v_column_name
LOOP
  EXECUTE format('SELECT ARRAY_AGG(row(tablename.*)::text) FROM %I.%I AS tablename WHERE %I::text ILIKE %s',
    rec.schemaname, rec.tablename, rec.colname, quote_literal(concat('%',v_column_value,'%'))) INTO v_row_array;
  IF v_row_array is not null THEN
    FOR rec2 IN
      SELECT unnest(v_row_array) AS one_row
    LOOP
      rec.entirerow := rec2.one_row;
      RETURN NEXT rec;
    END LOOP;
  END IF;
END LOOP;
END
$$;

示例性呼叫和输出

postgres=# select * from search_tables_for_column('id','5');

 schemaname | tablename | colname | entirerow
------------+-----------+---------+-----------
 public     | tbl1      | id      | (1,5)
 public     | tbl1      | id      | (1,25)
(2 rows)

I am trying to write sub-queries so that I search all tables for a column named id and since there are multiple tables with id column, I want to add the condition, so that id = 3119093.

My attempt was:

Select * 
from information_schema.tables 
where id = '3119093' and id IN (
    Select table_name 
    from information_schema.columns 
    where column_name = 'id' );

This didn't work so I tried:

Select * 
from information_schema.tables 
where table_name IN (
    Select table_name 
    from information_schema.columns 
    where column_name = 'id' and 'id' IN (
        Select * from table_name where 'id' = 3119093));

This isn't the right way either. Any help would be appreciated. Thanks!

A harder attempt is:

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE'
        --AND c.column_name = "id"
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) like %L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

select * from search_columns('%3119093%'::varchar,'{}'::name[]) ;

The only problem is this code displays the table name and column name. I have to then manually enter

Select * from table_name where id = 3119093

where I got the table name from the code above.

I want to automatically implement returning rows from a table but I don't know how to get the table name automatically.

解决方案

I took the time to make it work for you.

For starters, some information on what is going on inside the code.

Explanation

1. function takes two input arguments: column name and column value
2. it requires a created type that it will be returning a set of
3. first loop identifies tables that have a column name specified as the input argument
4. then it forms a query which aggregates all rows that match the input condition inside every table taken from step 3 with comparison based on ILIKE - as per your example
5. function goes into the second loop only if there is at least one row in currently visited table that matches specified condition (then the array is not null)
6. second loop unnests the array of rows that match the condition and for every element it puts it in the function output with RETURN NEXT rec clause

Notes

• Searching with LIKE is inefficient - I suggest adding another input argument "column type" and restrict it in the lookup by adding a join to pg_catalog.pg_type table.

• The second loop is there so that if more than 1 row is found for a particular table, then every row gets returned.

• If you are looking for something else, like you need key-value pairs, not just the values, then you need to extend the function. You could for example build json format from rows.

---

Now, to the code.

Test case

CREATE TABLE tbl1 (col1 int, id int); -- does contain values
CREATE TABLE tbl2 (col1 int, col2 int); -- doesn't contain column "id"
CREATE TABLE tbl3 (id int, col5 int); -- doesn't contain values

INSERT INTO tbl1 (col1, id)
  VALUES (1, 5), (1, 33), (1, 25);

Table stores data:

postgres=# select * From tbl1;

 col1 | id
------+----
    1 |  5
    1 | 33
    1 | 25
(3 rows)

Creating type

CREATE TYPE sometype AS ( schemaname text, tablename text, colname text, entirerow text );

Function code

CREATE OR REPLACE FUNCTION search_tables_for_column (
    v_column_name text
  , v_column_value text
)
RETURNS SETOF sometype
LANGUAGE plpgsql
STABLE
AS
$$
DECLARE
  rec           sometype%rowtype;
  v_row_array   text[];
  rec2          record;
  arr_el        text;
BEGIN
FOR rec IN
  SELECT 
      nam.nspname AS schemaname
    , cls.relname AS tablename
    , att.attname AS colname
    , null::text AS entirerow
  FROM 
    pg_attribute att
    JOIN pg_class cls ON att.attrelid = cls.oid 
    JOIN pg_namespace nam ON cls.relnamespace = nam.oid 
  WHERE 
    cls.relkind = 'r'
    AND att.attname = v_column_name
LOOP
  EXECUTE format('SELECT ARRAY_AGG(row(tablename.*)::text) FROM %I.%I AS tablename WHERE %I::text ILIKE %s',
    rec.schemaname, rec.tablename, rec.colname, quote_literal(concat('%',v_column_value,'%'))) INTO v_row_array;
  IF v_row_array is not null THEN
    FOR rec2 IN
      SELECT unnest(v_row_array) AS one_row
    LOOP
      rec.entirerow := rec2.one_row;
      RETURN NEXT rec;
    END LOOP;
  END IF;
END LOOP;
END
$$;

Exemplary call & output

postgres=# select * from search_tables_for_column('id','5');

 schemaname | tablename | colname | entirerow
------------+-----------+---------+-----------
 public     | tbl1      | id      | (1,5)
 public     | tbl1      | id      | (1,25)
(2 rows)

这篇关于Postgres在给定列上找到符合条件的数据库表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！