Postgres中的动态UNION ALL查询 [英] Dynamic UNION ALL query in Postgres

问题描述

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

We are using a Postgres / PostGis connection to get data that is published via a geoserver.

此刻查询如下:

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很陌生.

In our db only valid shapefiles will be imported each in a single table so it would make sense to make the UNION ALL part dynamic (loop over each table and make the UNION ALL statement). Is there a way to do this in a standard Postgres way or do I need to write a function and how would the syntax look like? I am pretty new to SQL.

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

The shapefiles have a different data structure and only the ogc_fid column and the wkb_geometry column are always available and we would like to union all tables from the DB.

推荐答案

这只是您需要详细了解语法的一般准则.

This is just general guidelines you need work in the details specially syntaxis.

您需要创建一个存储过程

You need create a store procedure

创建一个循环检查information_schema.tables过滤器以查找所需的表名

Create a loop checking information_schema.tables filter for the tablenames you want

DECLARE    
    rec record;
    strSQL text;
BEGIN

然后为每个表创建一个strSQL

Then create a strSQL with each table

 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;

这篇关于Postgres中的动态UNION ALL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！