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;