PostgreSQL：使用动态表名合并多个表

Question

我有一个包含约100张表的模式，名称为qgep，并且以vl_开头命名。 它们都具有相同的列（colA，colB，colC）。 我想做的是获取所有vl_*表的联合大表，并在其中添加一个原始表名称的列。 我可以获取表列表：

SELECT table_name
  FROM information_schema.tables 
  WHERE table_schema = 'qgep' 
  AND table_name LIKE 'vl_%'

我找到的唯一解决问题的方法是生成一个SQL命令以进一步执行它：

SELECT
  string_agg(
    'SELECT '''
    ||table_name
    ||''' AS table_name, colA, colB, colC FROM qgep.'
    ||table_name
  , ' UNION ')::text 
FROM information_schema.tables 
  WHERE table_schema = 'qgep' 
  AND table_name LIKE 'vl_%'"

执行这个SQL命令将输出我想要的结果。虽然，它的性能非常差，而且相当丑陋...

我想避免使用EXECUTE。 你有什么建议可以提供吗？ 是否可以使用WITH ... UNION ALL来完成某些操作？

继承会有帮助吗？从哪个类中选择记录是可能的吗？

- Denis Rouzaud

---

它们都有相同的列（colA，colB，colC）。向它们所有的表中添加一个名为“col_X”的列，并赋予不同的值，然后将这些表合并成一个大表。 - joop

动态SQL仅与EXECUTE一起使用（或在Shell脚本中 :)。 - Vao Tsun

我正在脚本中使用这个。不过，我希望能找到更好的方法。也许还能学到一些东西。 - Denis Rouzaud

你为什么想要一次性查询100个表？ - Jakub Kania

每个表格列出了值列表（选择下拉框）的可能选项，而这些选项以多种语言列出。我希望生成一个大表格，用作字典。 - Denis Rouzaud

Answer 1

create or replace function uall() returns table ( code integer ,
  value character varying(50),
  active boolean,tablename text ) AS $$ 
declare
  _i int;
  _r record;
  _t text := '';
begin 
  select distinct string_agg($s$select *,'$s$||table_name||$s$' from $s$||table_name,' union all ') into _t from information_schema.tables where table_name like 'vl_%';
return query execute _t;
end;$$ language plpgsql
;

select * from uall()

- Vao Tsun

---

非常感谢您的帮助。我想避免使用execute。最终，我找到了使用继承的解决方案。 - Denis Rouzaud

Answer 2

解决方案确实是使用继承，我最终在Postgres文档上找到了解决方案。

SELECT p.relname, vl.* 
FROM qgep.is_value_list_base vl, pg_class p 
WHERE vl.tableoid = p.oid;

- Denis Rouzaud