本文介绍了在PostgreSQL 9.2中重命名hstore密钥的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在评估PostgreSQL的hstore功能(9.2)和并未明确说明如何重命名密钥。例如,如何将键 c 重命名为 ai_count

I've been evaluating PostgreSQL's hstore functionality (9.2) and the only thing the fine manual isn't explicit about is how to rename keys. For instance, how could I rename the key c to ai_count?

c => 3, ai_voltage => 3, ai_temperature => 28

我想没有直接的方法可以做到,它涉及将 c 键复制到 ai_count 键,然后放下 c 键。我该怎么做,理想情况下是一种可以应用于多个记录的单行代码?

I'm thinking there is no direct way to do this and that it involves duplicating the c key to a ai_count key, then dropping the c key. How can I do this, ideally as a one-liner which can be applied to multiple records?

推荐答案

我认为您是正确的是,您必须将旧的一对拔出,然后将新的一对(使用重命名的钥匙)放回去。

I think you're right that you have to pull the old pair out and put the new pair (with the renamed key) back in.

您可以使用单衬套来完成:

You could do it with a one-liner:

(h - from_key) || hstore(to_key, h -> from_key)

其中 h 是hstore, from_key 是要更改的密钥,而 to_key 是要更改的密钥它来。这将返回一个具有所需更改的新hstore,但假定 from_key h 中;如果 from_key 不在 h 中,那么您将得到 to_key-> ;您的hstore中为NULL 。如果您像所有理智的人一样,不希望杂散NULL,那么我会将逻辑包装在一个简单的函数中,以使其更容易添加存在检查;像这样的东西:

where h is the hstore, from_key is the key you want to change and to_key is what you want to change it to. That will return a new hstore with the desired change but it assumes that from_key is in h; if from_key isn't in h then you'll end up with a to_key -> NULL in your hstore. If you, like all sane people, don't want the stray NULL then I'd wrap the logic in a simple function to make it easier to add an existence check; something like this:

create or replace function
change_hstore_key(h hstore, from_key text, to_key text) returns hstore as $$
begin
    if h ? from_key then
        return (h - from_key) || hstore(to_key, h -> from_key);
    end if;
    return h;
end
$$ language plpgsql;

然后您可以同时说这两个字并获得预期的结果:

Then you can say both of these and get the expected results:

=> select change_hstore_key('a=>1,b=>2,c=>3'::hstore, 'b', 'pancakes');
      change_hstore_key
------------------------------
 "pancakes"=>"2", "a"=>"1", "c"=>"3"

=> select change_hstore_key('a=>1,b=>2,c=>3'::hstore, 'pancakes', 'X');
      change_hstore_key
------------------------------
 "a"=>"1", "b"=>"2", "c"=>"3"

这篇关于在PostgreSQL 9.2中重命名hstore密钥的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 18:40