sql - Postgres 中的动态 UNION ALL 查询

Question

我们正在使用 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 列始终可用，我们希望合并数据库中的所有表。

Answer 1

这只是您需要在细节上特别是语法上工作的一般指南。

你需要创建一个存储过程

为你想要的表名创建一个循环检查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;