Postgres 中的动态 UNION ALL 查询

我们使用 Postgres / PostGis 连接来获取通过地理服务器发布的数据。

查询现在看起来像这样：

SELECT 
    row_number() over (ORDER BY a.ogc_fid) AS qid, a.wkb_geometry AS geometry
FROM
(
   SELECT * FROM test
   UNION ALL
   SELECT * FROM test1
   UNION ALL
   SELECT * FROM test2
)a

在我们的数据库中，只有有效的 shapefile 才会导入到单个表中，因此使 UNION ALL 部分动态化（循环每个表并生成 UNION ALL 语句）是有意义的。有没有办法以标准的 Postgres 方式做到这一点，或者我是否需要编写一个函数以及语法是什么样的？我对 SQL 还很陌生。

shapefile 具有不同的数据结构，只有 ogc_fid 列和 wkb_geometry 列始终可用，我们希望合并数据库中的所有表。

这只是一般准则，您需要处理细节，特别是语法。

您需要创建一个存储过程

创建循环检查information_schema.tables过滤你想要的表名

DECLARE    
    rec record;
    strSQL text;
BEGIN

然后为每个表创建一个strSQL

 FOR rec IN SELECT table_schema, table_name
            FROM information_schema.tables                
 LOOP
     strSQL := strSQL || 'SELECT ogc_fid, wkb_geometry FROM ' || 
               rec.table_schema || '.' || rec.table_name || ' UNION ';
 END LOOP;

-- have to remove the last ' UNION ' from strSQL    

strSQL := 'SELECT  row_number() over (ORDER BY a.ogc_fid) AS qid,
         a.wkb_geometry AS geometry FROM (' || strSQL || ')';

EXECUTE strSQL;