sql - 如何从一个表中选择值，该表的名称是从另一个表派生的？

Question

我有一个名为 folder 的表，它存储其他表的名称(名为 fileXXX，其中 X 是一个数字)，具有相同的结构，在相同的 Postgres 中数据库。

我想构建一个 SQL 语句，从 folder 表中检索数据库中所有 fileXXX 表的名称，并创建一个具有此结构的 SQL 语句

SELECT * FROM _file001_
  UNION
  SELECT * FROM _file002_
  UNION
  SELECT * FROM _file003_
 ...

我找到了很多关于如何在 WHERE 子句中使用 SELECT 语句的示例，但是没有一个以这种方式在 FROM 子句中使用一个。

Answer 1

可以为此编写一个函数 ( see here )

demo:db<>fiddle

1. 从信息模式中查询所有表名:

   SELECT table_name 
   FROM information_schema.tables 
   WHERE table_name LIKE 'file%'
   

2. 而不是 SELECT table_name 将包含表名写入查询字符串

   SELECT 
       'SELECT * FROM ' || table_name
   ...
   

3. 用string_agg对每个结果行进行分组，使用UNION ALL作为分隔符:

   SELECT
       string_agg(/*see (2)*/, ' UNION ALL ')
   ...
   

4. 这会导致您在问题中提到的查询。

5. 最后这个字符串可以解释为真正的查询并且可以在这个函数中执行:

   CREATE OR REPLACE function union_all() returns table (ids int) AS $$ 
   declare
       _t text := '';
   begin 
       SELECT 
           string_agg('SELECT * FROM ' || table_name, ' UNION ALL ') 
               into _t 
       FROM information_schema.tables 
       WHERE table_name LIKE 'file%';
   
       return query execute _t;
   end;$$ language plpgsql;
   

6. 调用这个函数:

   SELECT * FROM union_all()