python - 从架构中的每个表中获取所有单列

Question

在我们的系统中，我们有 1000 多个表，每个表都有一个包含 DateTime 对象的“日期”列。我想获得一个列表，其中包含所有表中存在的每个日期。我确信应该有一种简单的方法可以做到这一点，但我对 postgresql 或 sqlalchemy 的了解非常有限。

在 postgresql 中，我可以对两个表进行完全连接，但似乎没有办法对模式中的每个表进行单个公共(public)字段的连接。

然后我尝试使用 sqlalchemy 在 python 中以编程方式解决这个问题。对于每个表，我确实为“日期”列创建了一个不同的选择，然后将该选择列表设置为 CompoundSelect 对象的 selects 属性，然后执行。正如人们可能对一个丑陋的暴力查询所期望的那样，它现在已经运行了一个小时左右，我不确定它是否在某处悄无声息地崩溃并且永远不会恢复。

有没有更干净、更好的方法来做到这一点？

Answer 1

您肯定希望在服务器而不是应用程序级别执行此操作，因为应用程序和服务器之间的多次往返以及中间结果中可能存在重复的数据。

由于您需要处理 1,000 多个表，因此您应该使用系统目录并动态查询表。你需要一个函数来有效地做到这一点:

CREATE FUNCTION get_all_dates() RETURNS SETOF date AS $$
DECLARE
  tbl    name;
BEGIN
  FOR tbl IN SELECT 'public.' || tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    RETURN QUERY EXECUTE 'SELECT DISTINCT date::date FROM ' || tbl;
  END LOOP
END; $$ LANGUAGE plpgsql;

这将处理 public 模式中的所有表；根据需要更改。如果表在多个模式中，您需要在表的存储位置插入额外的逻辑，或者您可以将模式名称作为函数的参数并多次调用函数并 UNION 结果。

请注意，您可能会从多个表中获得重复的 date。您可以在调用该函数的语句中清除这些重复项:

SELECT DISTINCT * FROM get_all_dates() ORDER BY 1;

该函数在内存中创建一个结果集，但如果 1,000+ 表中的行中不同日期的数量非常大，结果将写入磁盘。如果您希望发生这种情况，那么最好在函数开头创建一个临时表并将日期插入该临时表。