Postgres中的动态UNION ALL查询
Postgres中的动态UNION ALL查询
提问于 2016-02-18 15:48:03
我们使用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在我们的db中，只有有效的shapefiles将被导入到一个表中，因此使UNION部件动态(在每个表上循环并使UNION语句)是有意义的。是否有一种以标准Postgres方式完成此操作的方法，或者我是否需要编写一个函数，语法是什么样的呢？我对SQL非常陌生。
shapefiles具有不同的数据结构，只有ogc_fid列和wkb_geometry列总是可用的，我们希望合并DB中的所有表。
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-02-18 16:30:27
这只是一般的指导方针，你需要在细节上工作，特别是合合。
您需要创建一个存储过程
为所需的表名创建一个检查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;Stack Overflow用户
发布于 2016-02-18 18:11:34
一种解决方案是使用json将其余的列序列化为json()。(可从PostgreSQL9.2获得)。对于PG9.1 (及更早版本)，您可以使用hstore，但请注意，所有值都转换为文本。
为什么连载？在列数不同或联合查询之间的数据类型不匹配的情况下，友联市行是不可能的。
我创建了一个快速示例来说明：
--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}"页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35486057
复制相关文章
相似问题
腾讯云开发者
