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

问题描述

我在名为 qgep 的模式中有一组表（约100个），其名称以 vl _ 开头。
它们具有相同的列（colA，colB，colC）。

I have a set of tables (about 100) in schema named qgep and which names start with vl_.They have all the same columns (colA, colB, colC).

我想做的就是得到一张大桌子，是我所有 vl _ * 表的联合，还有一列带有原始表名称的列。

What I'd like to do is to get one big table which is the union of all my vl_* tables, with also a column with the name of the original table.

我可以获得表的列表：

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

我发现要解决我的问题是生成一条SQL命令以进一步执行它：

The only way I found to solve my problem is to generate a SQL command to execute it further:

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命令将输出我想要的内容。

Then executing this SQL command will output what I want. Although, it is very not performant, and quite ugly...

我想避免使用 EXECUTE 。
您对寻找什么有任何建议吗？
有什么我可以使用 WITH ... UNION ALL 吗？

会继承对我有帮助吗？是否可以知道 select 中的记录是哪个类的？

Would inheritance help me? Is it possible to know from which class is the record in the select?

推荐答案

该解决方案确实是使用继承的，我终于在。

The solution was indeed to use inheritance, and I finally found the solution on the Postgres doc.

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

这篇关于PostgreSQL：使用动态名称合并多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！