postgresql - 如何将 Hstore 键和值分解为 postgres 中的单独列

Question

我有一个 osm 表，其中包含在名为 tags 的 hstore 列中找到的所有标签。

我想编写一个查询，该查询将返回一个表，该表包含表的所有列和每个标签的附加列，每个标签都将每个键作为列名及其下的值。我该怎么做？

所以我的输入将是:

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|

Answer 1

这是一个接受表名和键列表的函数。然后它创建新的表和列。它演示了如何在函数内执行动态命令。最后的 SELECT 显示了如何提取 key 。填充表格可以通过在创建列后循环遍历现有表格的行来完成。几乎可以肯定有一种更优雅/更有效的方法来完成所有这些:)

创建或替换函数 explode_hstore(tname text, keys TEXT[]) RETURNS VOID AS $$
宣布
k 文本；
开始
RAISE NOTICE 'Creating table=%', tname;
执行“创建表” || tname || '()';
FOREACH k IN ARRAY 键
环形
RAISE NOTICE 'Adding column for key=%', k;
执行“更改表” || tname || '添加列' || k || ' 文本';
结束循环；
结尾;
$$ 语言 plpgsql；


运行函数:

SELECT explode_hstore('exploded_hstore',
(select array_agg(x.keys) FROM (SELECT skeys("data") AS keys
FROM hstoretest GROUP BY keys) x))