sql - 如何在多个表上构建动态查询

Question

我不确定如何制定此查询。我想我需要一个子查询？这基本上是我在单个查询中尝试执行的操作。

此查询为我提供了我需要的表格列表:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'abc_dev_12345' 
AND table_name like 'fact_%';

对于给定的表列表，然后我想对每个表名进行计数(每个表名具有相同的列信息，我需要查询)

SELECT table_name,
       count (domain_key) key_count,
       domain_key,
       form_created_datetime
FROM (List of tables above)
GROUP BY domain_key,
         form_created_datetime;

我可以遍历第一个查询中列出的每个表来进行计数吗？在单个查询中执行此操作？

所以预期的结果与此类似:

table_name |  key_count | domain_key | form_created_datetime
--------------------------------------------------------------
fact_1        1241                 5   2015-09-22 01:47:36.136789
fact_2          32                 9   2015-09-22 01:47:36.136789

示例数据:

abc_dev_12345=> SELECT *
FROM information_schema.tables
where table_schema='abc_dev_own_12345'
and table_name='fact_1';
 table_catalog |   table_schema    |     table_name     | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | use
r_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+-------------------+--------------------+------------+------------------------------+----------------------+---------------------------+--------------------------+----
--------------------+--------------------+----------+---------------
 abc_dev_12345 | abc_dev_own_12345 | fact_1 | BASE TABLE |                              |                      |                           |                          |
                    | YES                | NO       |
(1 row)


abc_dev_12345=> SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'abc_dev_own_12345'
  AND table_name   = 'fact_1';
      column_name
------------------------
 email_date_key
 email_time_key
 customer_key
 form_created_datetime
 client_key
 domain_key

Answer 1

正如 Eelke 和 Craig Ringer 指出的那样，您需要在 plpgsql 函数中进行动态查询。您要应用于每个表的基本语句是:

SELECT <table_name>, count(domain_key) AS key_count, domain_key, form_created_datetime
FROM <table_name> GROUP BY 3, 4

并且你想UNION一起。

最有效的方法是首先根据 information_schema.tables 中的信息将查询构建为 text 对象，然后EXECUTE 该查询。有很多方法可以构建该查询，但我特别喜欢下面使用 string_agg() 的肮脏技巧:

CREATE FUNCTION table_domains()
RETURNS TABLE (table_name varchar, key_count bigint, domain_key integer, form_created_datetime timestamp)
AS $$
DECLARE
  qry text;
BEGIN
  -- format() builds query for individual table
  -- string_agg() UNIONs queries from all tables into a single statement
  SELECT string_agg(
    format('SELECT %1$I, count(domain_key), domain_key, form_created_datetime
            FROM %1$I GROUP BY 3, 4', table_name),
    ' UNION ') INTO qry
  FROM information_schema.tables 
  WHERE table_schema = 'abc_dev_12345' 
    AND table_name LIKE 'fact_%';

  -- Now EXECUTE the query
  RETURN QUERY EXECUTE qry;
END;
$$ LANGUAGE plpgsql;

不需要循环或游标，非常高效。

像使用任何其他表一样使用:

SELECT * FROM table_domains();