我有一个osm表,其中包含hstore列中名为tags的所有标记。
我想编写一个查询,它将返回一个表,其中包含该表的所有列,以及每个标记的附加列,每个标记的列名和值都是每个键。我该怎么做?
所以我的意见是:
Item_id | tags |
----------+-------+
1614 | apple=2,bees=150|
1938 | apple=1,bees=50 |
1983 | apple=1,bees=50 |
1322 | apple=1,bees=100|
输出将是脚本的
item_id | apple | bees
---------+-------+------
1614 | 2 | 150
1938 | 1 | 50
1983 | 1 | 50
1322 | 1 | 100
考虑到每一行有不同数量的标记,我不知道每一列的计数和键是什么,所以我该如何进行例如
Item_id | tags |
----------+-------+
1614 | apple=2,bees=150,green=345|
1938 | apple=1,bees=50 |
1983 | apple=1,bees=50 |
1322 | apple=1,bees=100,red=346|
输出为:
item_id | apple | bees | green | red|
---------+-------+----+--------+----+
1614 | 2 | 150 |345| NULL|
1938 | 1 | 50 |NULL| NULL|
1983 | 1 | 50 |NULL| NULL|
1322 | 1 | 100 |NULL| 346|
最佳答案
下面是一个接受表名和键列表的函数。然后创建新的表和列。它演示了如何在函数中执行动态命令。最后的选择显示了如何提取关键点。在创建列之后,可以通过循环现有表的行来填充表。几乎可以肯定的是,有一种更优雅/高效的方法可以做到这一切:)CREATE OR REPLACE FUNCTION explode_hstore(tname text, keys TEXT[]) RETURNS VOID AS $$DECLARE k TEXT;BEGIN RAISE NOTICE 'Creating table=%', tname; EXECUTE 'CREATE TABLE ' || tname || ' ()'; FOREACH k IN ARRAY keys LOOP RAISE NOTICE 'Adding column for key=%', k; EXECUTE 'ALTER TABLE ' || tname || ' ADD COLUMN ' || k || ' TEXT'; END LOOP;END;$$ LANGUAGE plpgsql;
要运行函数:SELECT explode_hstore('exploded_hstore', (select array_agg(x.keys) FROM (SELECT skeys("data") AS keys FROM hstoretest GROUP BY keys) x))