简化 PostgreSQL 中复杂的 union all

Question

我在 PostgreSQL 中有一个非常复杂的查询，它联合了多个表，所有这些表都有我们想要联合的公共字段集。目前我们正在预生成此查询。我已经看到使用 UNPIVOT 的解决方案，我想知道是否可以在 PostgreSQL 风格的 SQL 中执行此操作。

我所拥有的就是

SELECT a,b,c FROM a UNION ALL
SELECT a,b,c FROM c UNION ALL
SELECT a,b,c FROM d UNION ALL
SELECT a,b,c FROM e UNION ALL
SELECT a,b,c FROM f

我想要将表的名称合并到单独的表中并将其用于此查询。

附言。更改架构不是一个选项。

Answer 1

使用 Postgres 文档的继承来实现继承。您需要重新创建数据库，但是如果您转储没有架构的表，创建具有继承的新架构，然后加载回数据，那么这很容易。

该架构看起来像这样：

CREATE TABLE base (a, b, c);
CREATE TABLE a () INHERITS (base);
CREATE TABLE b () INHERITS (base);
....

通过此设计，您可以进行简单的选择：

SELECT * FROM base;

这将返回 base 中的所有行以及从 base 继承的所有表。

如果您还没有阅读过有关 PostgreSQL 表分区的文档，请参阅已经这样做了。

Answer 2

如果你真的无法修复你的设计（或者不想使用 jmz 的非常好的建议），你唯一的选择可能是一个集合返回函数，它“即时”构建必要的 UNION，然后返回结果那。

create or replace function my_union()
 returns table(a integer, b integer, c integer)
as
$body$
declare
  union_cursor refcursor;
  table_cursor cursor for SELECT table_name FROM union_source; 
  union_query text := '';
begin

  -- build the query string for the union
  for table_list_record in table_cursor loop
    if union_query  '' then 
      union_query := union_query||' UNION ALL';
    end if;
    union_query := union_query||' SELECT a,b,c FROM '||table_list_record.table_name;
  end loop;

  -- run the union and return the result
  for a,b,c IN EXECUTE union_query LOOP
    return next;
  end loop; 
end;
$body$
language plpgsql;

该函数根据 union_source 中的表名构建必要的 UNION，然后执行 union 并返回结果。

如果列并不总是具有相同的名称，您可以扩展 union_source 表来存储每个表的列列表。

要使用此功能，只需从中选择：

select *
from my_union()