首页 > 解决方案 > 在 PostgreSQL 中,是否可以查询给定数据库中的所有表?

问题描述

在 PostgreSQL 中,是否可以查询给定数据库中的所有表以获取满足某些条件的记录?

Select [dbname "db"] *
From [all tables in database "db"]
Where [some condition on records]

[] 里面的东西是任意的,直到它们通常应该表示的意思。

标签: sqlpostgresql

解决方案


我最好的选择是,您希望从特定数据库中的每个表中返回满足特定条件的所有行。这变得很棘手,因为:

  1. 您的条件所基于的表必须存在于您正在查询的每个表中。
  2. 此外,因为您在这个模式/数据库中的许多表可能有不同的列,所以您不能在同一个结果集中将它们全部返回。
  3. 没有“一个” sql 语句可以发送到数据库来执行此操作。您必须至少查询两次。一次获取表列表,然后再次查询这些表。

考虑一个包含两个表的数据库/模式,这两个表都带有 列F1。我们想要此模式中的表中的所有记录,其中F1=1. 所以:

# CREATE TABLE public.t2 (f1 int, f2 varchar(20), f3 date);
    CREATE TABLE
# CREATE TABLE public.t1 (f1 int, f4 varchar(40), f5 varchar(10), f6 int);
    CREATE TABLE
# SELECT * FROM information_schema.tables WHERE table_schema = 'public';
     table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
    ---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
     username      | public       | t2         | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
     username      | public       | t1         | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
    (2 rows)

# SELECT 'SELECT * FROM public.' || table_name || ' WHERE f1=1;' FROM 
    information_schema.tables WHERE table_schema = 'public';
                  ?column?               
    -------------------------------------
     SELECT * FROM public.t2 WHERE f1=1;
     SELECT * FROM public.t1 WHERE f1=1;
    (2 rows)

最后一条 SQL 会生成两条记录(架构中的每个表都有一条记录),并在其中查询您的条件为真的记录。您现在可以分别执行其中的每一项以获得结果(在两个结果集中)。

# SELECT * FROM public.t2 WHERE f1=1;SELECT * FROM public.t1 WHERE f1=1;
     f1 | f2 | f3 
    ----+----+----
    (0 rows)

     f1 | f4 | f5 | f6 
    ----+----+----+----
    (0 rows)

如果您的架构中的每个表都完全相同(相同的列数、列的顺序和数据类型),那么您可以查询information_Schema并构建一个 UNION 查询,然后分别执行:

# CREATE TABLE public.t1 (f1 int, f2 varchar(20), f3 date);
    CREATE TABLE
# CREATE TABLE public.t2 (f1 int, f2 varchar(20), f3 date);
    CREATE TABLE
# INSERT INTO public.t1 VALUES (1, 'test', '2019-04-22'),(2, 'test2', '2019-04-22');
    INSERT 0 2
# INSERT INTO public.t2 VALUES (1, 'testtest', '2019-04-21'),(2, 'testest2', '2019-04-20');
    INSERT 0 2
# SELECT 'SELECT * FROM public.' || table_name || ' WHERE f1=1' || CASE WHEN LEAD(table_name) OVER (ORDER BY table_name) IS NOT NULL THEN ' UNION ALL' ELSE ';' END FROM information_schema.tables WHERE table_schema = 'public';
                       ?column?                   
    ----------------------------------------------
     SELECT * FROM public.t1 WHERE f1=1 UNION ALL
     SELECT * FROM public.t2 WHERE f1=1;
    (2 rows)
# SELECT * FROM public.t1 WHERE f1=1 UNION ALL
-#  SELECT * FROM public.t2 WHERE f1=1;
     f1 |    f2    |     f3     
    ----+----------+------------
      1 | test     | 2019-04-22
      1 | testtest | 2019-04-21
    (2 rows)

您可以看到这两个选项都不漂亮,因为您必须动态编写 SQL,然后单独执行它。


推荐阅读