Postgres中的动态UNION ALL查询

Question

我们正在使用Postgres/PostGis连接来获取通过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还比较陌生。

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

- Bernd Loigge

---

我不太清楚这个shapefile是什么，但为什么不把所有东西都导入到一个单独的表中呢？另一个选择是使用表继承。 - user330315

您的问题并不是很清晰，所以答案取决于您想要做什么。但简单的解决方法是创建一个动态查询，但您需要知道要连接哪些表。 - Juan Carlos Oropeza

在shapefiles中，您可以存储地理数据。我们希望避免将所有内容存储在单个表中，因为地理数据的数据结构可能不同，并且我们希望保留每个文件的所有列以供其他查询使用。 - Bernd Loigge

@JuanCarlosOropeza - 我更新了我的问题。在这个特定的数据库中，所有的表都需要被联合起来。 - Bernd Loigge

如果你想在每个表中拥有不同的列，那么你不可能在一个联合语句中使用它们。 - user330315

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;

- Juan Carlos Oropeza

---

@ClodoaldoNeto 你怎么会认为这是SQL Server呢？ - Juan Carlos Oropeza

谢谢你的回答 - 非常有帮助。最终我使用了 UNION ALL。顺便说一下，我认为有一个小错误：应该是 "rec.table_name || " 而不是 "rec.table_name + "。 - Bernd Loigge

你说得对，有时我会混淆SQL Server和Postgres的语法。 - Juan Carlos Oropeza

@JuanCarlosOropeza 我正在尝试使用您的解决方案，但是我遇到了一个语法错误，附近是record.. 有什么想法吗？ - lookslikeanevo

从字符串中删除最后一个UNION可以这样实现：strSQL := left(strSQL, -6); - Timothy Dalton

Answer 2

一种解决方案是使用json和row_to_json()将其余列序列化。（自PostgreSQL9.2起可用）。 对于PG9.1（及更早版本），您可以使用hstore，但请注意，所有值都会转换为文本。 为什么要序列化？无法union行，其中列的数量不同，或者联合查询之间的数据类型不匹配。 我创建了一个快速示例来说明：

--DROP SCHEMA testschema CASCADE;
CREATE SCHEMA testschema;

CREATE TABLE testschema.test1 (
    id integer,
    fid integer,
    metadata text
);

CREATE TABLE testschema.test2 (
    id integer,
    fid integer,
    city text,
    count integer
);

CREATE TABLE testschema.test3 (
    id integer,
    fid integer
);


INSERT INTO testschema.test1 VALUES (1,   4450, 'lala');
INSERT INTO testschema.test2 VALUES (33,  6682, 'London', 12345);
INSERT INTO testschema.test3 VALUES (185, 8991);


SELECT 
    row_number() OVER (ORDER BY a.fid) AS qid, a.*
FROM
(
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test1 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test2 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test3 t    
) a

选择输出：

qid  id    fid    jsondoc
1;   1;    4450;  "{"id":1,"fid":4450,"metadata":"lala"}"
2;   33;   6682;  "{"id":33,"fid":6682,"city":"London","count":12345}"
3;   185;  8991;  "{"id":185,"fid":8991}"

- Geir Bostad