X-Git-Url: http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=blobdiff_plain;f=expected%2Fnested.out;fp=expected%2Fnested.out;h=f7df8cfa969603995a6bbc8f5721a5ec81ad4ac1;hp=0000000000000000000000000000000000000000;hb=2d3cb5062568eab105ed554350ac99bae76ee0ec;hpb=77af220c462dd61507d6cca9b9f54ad3e102e1b6 diff --git a/expected/nested.out b/expected/nested.out new file mode 100644 index 0000000..f7df8cf --- /dev/null +++ b/expected/nested.out @@ -0,0 +1,2261 @@ +SELECT 'ff => {a=>12, b=>16}'::hstore; + hstore +-------------------------- + "ff"=>{"a"=>12, "b"=>16} +(1 row) + +SELECT 'ff => {a=>12, b=>16}, qq=> 123'::hstore; + hstore +------------------------------------- + "ff"=>{"a"=>12, "b"=>16}, "qq"=>123 +(1 row) + +SELECT 'aa => {a,aaa}, qq=>{ a=>12, b=>16 , c=> { c1, c2}, d=>{d1=>d1, d2=>d2, d1=>d3} }'::hstore; + hstore +------------------------------------------------------------------------------------------------ + "aa"=>["a", "aaa"], "qq"=>{"a"=>12, "b"=>16, "c"=>["c1", "c2"], "d"=>{"d1"=>"d3", "d2"=>"d2"}} +(1 row) + +SELECT '"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore; + hstore +---------------------------------------------------------------------------------------------------- + "aa"=>["a", "aaa"], "qq"=>{"a"=>"12", "b"=>"16", "c"=>["c1", "c2"], "d"=>{"d1"=>"d1", "d2"=>"d2"}} +(1 row) + +SELECT '"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2,{c3},{c4=>4}}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore; + hstore +----------------------------------------------------------------------------------------------------------------------- + "aa"=>["a", "aaa"], "qq"=>{"a"=>"12", "b"=>"16", "c"=>["c1", "c2", ["c3"], {"c4"=>4}], "d"=>{"d1"=>"d1", "d2"=>"d2"}} +(1 row) + +SELECT 'ff => {a,aaa}'::hstore; + hstore +-------------------- + "ff"=>["a", "aaa"] +(1 row) + +select 'null'::hstore; + hstore +-------- + NULL +(1 row) + +select '{null}'::hstore; + hstore +-------- + [NULL] +(1 row) + +select ''::hstore; + hstore +-------- + +(1 row) + +select '{}'::hstore; + hstore +-------- + +(1 row) + +--test optional outer braces +SELECT 'a=>1'::hstore; + hstore +-------- + "a"=>1 +(1 row) + +SELECT '{a=>1}'::hstore; + hstore +-------- + "a"=>1 +(1 row) + +SELECT '{a,b}'::hstore; + hstore +------------ + ["a", "b"] +(1 row) + +SELECT '{a,{b}}'::hstore; + hstore +-------------- + ["a", ["b"]] +(1 row) + +SELECT '{{a},b}'::hstore; + hstore +-------------- + [["a"], "b"] +(1 row) + +SELECT '{a,{b},{c}}'::hstore; + hstore +--------------------- + ["a", ["b"], ["c"]] +(1 row) + +SELECT '{{a},{b},c}'::hstore; + hstore +--------------------- + [["a"], ["b"], "c"] +(1 row) + +SELECT '{{a},b,{c}}'::hstore; + hstore +--------------------- + [["a"], "b", ["c"]] +(1 row) + +SELECT '{a,{b=>1}}'::hstore; + hstore +----------------- + ["a", {"b"=>1}] +(1 row) + +SELECT '{{a},{b=>1}}'::hstore; + hstore +------------------- + [["a"], {"b"=>1}] +(1 row) + +SELECT 'a'::hstore; + hstore +-------- + "a" +(1 row) + +SELECT '{a}'::hstore; + hstore +-------- + ["a"] +(1 row) + +SELECT ''::hstore; + hstore +-------- + +(1 row) + +SELECT '{}'::hstore; + hstore +-------- + +(1 row) + +--nested json +SELECT hstore_to_json('a=>1'); + hstore_to_json +---------------- + {"a": 1} +(1 row) + +SELECT hstore_to_json('{a=>1}'); + hstore_to_json +---------------- + {"a": 1} +(1 row) + +SELECT hstore_to_json('{a,b}'); + hstore_to_json +---------------- + ["a", "b"] +(1 row) + +SELECT hstore_to_json('{a,{b}}'); + hstore_to_json +---------------- + ["a", ["b"]] +(1 row) + +SELECT hstore_to_json('{{a},b}'); + hstore_to_json +---------------- + [["a"], "b"] +(1 row) + +SELECT hstore_to_json('{a,{b},{c}}'); + hstore_to_json +--------------------- + ["a", ["b"], ["c"]] +(1 row) + +SELECT hstore_to_json('{{a},{b},c}'); + hstore_to_json +--------------------- + [["a"], ["b"], "c"] +(1 row) + +SELECT hstore_to_json('{{a},b,{c}}'); + hstore_to_json +--------------------- + [["a"], "b", ["c"]] +(1 row) + +SELECT hstore_to_json('{a,{b=>1}}'); + hstore_to_json +----------------- + ["a", {"b": 1}] +(1 row) + +SELECT hstore_to_json('{{a},{b=>1}}'); + hstore_to_json +------------------- + [["a"], {"b": 1}] +(1 row) + +SELECT hstore_to_json('{{a},{b=>1},{c}}'); + hstore_to_json +-------------------------- + [["a"], {"b": 1}, ["c"]] +(1 row) + +SELECT hstore_to_json('a'); + hstore_to_json +---------------- + "a" +(1 row) + +SELECT hstore_to_json('{a}'); + hstore_to_json +---------------- + ["a"] +(1 row) + +SELECT hstore_to_json(''); + hstore_to_json +---------------- + {} +(1 row) + +SELECT hstore_to_json('{}'); + hstore_to_json +---------------- + {} +(1 row) + +SELECT hstore_to_json('"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2,{c3},{c4=>4}}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore); + hstore_to_json +------------------------------------------------------------------------------------------------------------------------- + {"aa": ["a", "aaa"], "qq": {"a": "12", "b": "16", "c": ["c1", "c2", ["c3"], {"c4": 4}], "d": {"d1": "d1", "d2": "d2"}}} +(1 row) + +-- +SELECT 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'ff', + 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'qq', + ('ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'Y') IS NULL AS t, + 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'x'; + ?column? | ?column? | t | ?column? +------------------+----------+---+---------- + "a"=>12, "b"=>16 | 123 | t | [1, 2] +(1 row) + +SELECT '[ a, b, c, d]'::hstore -> 'a'; + ?column? +---------- + a +(1 row) + +-- +CREATE TABLE testtype (i int, h hstore, a int[], j json); +INSERT INTO testtype VALUES (1, 'a=>1', '{1,2,3}', '{"x": 2}'); +SELECT populate_record(v, 'i=>2'::hstore) FROM testtype v; + populate_record +--------------------------------------- + (2,"""a""=>1","{1,2,3}","{""x"": 2}") +(1 row) + +SELECT populate_record(v, 'i=>2, a=>{7,8,9}'::hstore) FROM testtype v; + populate_record +--------------------------------------- + (2,"""a""=>1","{7,8,9}","{""x"": 2}") +(1 row) + +SELECT populate_record(v, 'i=>2, h=>{b=>3}, a=>{7,8,9}'::hstore) FROM testtype v; + populate_record +--------------------------------------- + (2,"""b""=>3","{7,8,9}","{""x"": 2}") +(1 row) + +SELECT populate_record(v, 'i=>2, h=>{b=>3}, a=>{7,8,9}, j=>{a=>{1,2,3}}'::hstore) FROM testtype v; + populate_record +----------------------------------------------- + (2,"""b""=>3","{7,8,9}","{""a"": [1, 2, 3]}") +(1 row) + +--complex delete +SELECT 'b=>{a,c}'::hstore - 'a'::text; + ?column? +----------------- + "b"=>["a", "c"] +(1 row) + +SELECT 'b=>{a,c}, a=>1'::hstore - 'a'::text; + ?column? +----------------- + "b"=>["a", "c"] +(1 row) + +SELECT 'b=>{a,c}, a=>[2,3]'::hstore - 'a'::text; + ?column? +----------------- + "b"=>["a", "c"] +(1 row) + +SELECT 'b=>{a,c}, a=>[2,3]'::hstore - 'a'::text; + ?column? +----------------- + "b"=>["a", "c"] +(1 row) + +SELECT '[2,3,a]'::hstore - 'a'::text; + ?column? +---------- + [2, 3] +(1 row) + +SELECT '[a,2,3,a]'::hstore - 'a'::text; + ?column? +---------- + [2, 3] +(1 row) + +SELECT '[a,a]'::hstore - 'a'::text; + ?column? +---------- + +(1 row) + +SELECT '[a]'::hstore - 'a'::text; + ?column? +---------- + +(1 row) + +SELECT 'a=>1'::hstore - 'a'::text; + ?column? +---------- + +(1 row) + +SELECT ''::hstore - 'a'::text; + ?column? +---------- + +(1 row) + +SELECT '{a, 1 , b,2, c,3}'::hstore - ARRAY['d','b']; + ?column? +--------------------- + ["a", 1, 2, "c", 3] +(1 row) + +SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v'::hstore; + ?column? +----------------------- + "a"=>[1, 2], "b"=>"c" +(1 row) + +SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v=>23'::hstore; + ?column? +----------------------- + "a"=>[1, 2], "b"=>"c" +(1 row) + +SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v=>{1,2}'::hstore; + ?column? +-------------------------------- + "a"=>[1, 2], "b"=>"c", "v"=>23 +(1 row) + +SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'a=>{1,2}'::hstore; + ?column? +------------------- + "b"=>"c", "v"=>23 +(1 row) + +SELECT '{a, {1,2}, v, 23, b, c}'::hstore - 'v'::hstore; + ?column? +----------------------------- + ["a", [1, 2], 23, "b", "c"] +(1 row) + +SELECT '{a, {1,2}, v, 23, b, c}'::hstore - 'v=>23'::hstore; + ?column? +------------------------- + ["a", [1, 2], "b", "c"] +(1 row) + +SELECT '{a, {1,2}, v, 23, b, c}'::hstore - '[v,23]'::hstore; + ?column? +------------------------- + ["a", [1, 2], "b", "c"] +(1 row) + +SELECT '{a, {1,2}, v, 23, b, c}'::hstore - '[v,{1,2}]'::hstore; + ?column? +--------------------- + ["a", 23, "b", "c"] +(1 row) + +--joining +SELECT 'aa=>1 , b=>2, cq=>3'::hstore || '{cq,l, b,g, fg,f, 1,2}'::hstore; + ?column? +----------------------------------------------- + "1"=>2, "b"=>"g", "aa"=>1, "cq"=>"l", "fg"=>f +(1 row) + +SELECT '{aa,1 , b,2, cq,3}'::hstore || '{cq,l, b,g, fg,f, 1,2}'::hstore; + ?column? +---------------------------------------------------------------- + ["aa", 1, "b", 2, "cq", 3, "cq", "l", "b", "g", "fg", f, 1, 2] +(1 row) + +SELECT 'x'::hstore || 'a=>"1"':: hstore; + ?column? +----------------- + ["x", "a", "1"] +(1 row) + +--slice +SELECT slice_array(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']); + slice_array +------------------ + {NULL,NULL,NULL} +(1 row) + +SELECT slice_array(hstore '{aa,1, b,2, c,3}', ARRAY['g','h','i']); + slice_array +------------------ + {NULL,NULL,NULL} +(1 row) + +SELECT slice_array(hstore 'aa=>1, b=>2, c=>3', ARRAY['b','c']); + slice_array +------------- + {2,3} +(1 row) + +SELECT slice_array(hstore '{aa,1, b,2, c,3}', ARRAY['b','c']); + slice_array +------------- + {b,c} +(1 row) + +SELECT slice_array(hstore 'aa=>1, b=>{2=>1}, c=>{1,2}', ARRAY['b','c']); + slice_array +----------------------- + {"\"2\"=>1","[1, 2]"} +(1 row) + +SELECT slice(hstore '{aa=>1, b=>2, c=>3}', ARRAY['g','h','i']); + slice +------- + +(1 row) + +SELECT slice(hstore '{aa,1, b,2, c,3}', ARRAY['g','h','i']); + slice +------- + +(1 row) + +SELECT slice(hstore '{aa=>1, b=>2, c=>3}', ARRAY['b','c']); + slice +---------------- + "b"=>2, "c"=>3 +(1 row) + +SELECT slice(hstore '{aa,1, b,2, c,3}', ARRAY['b','c']); + slice +------------ + ["b", "c"] +(1 row) + +SELECT slice(hstore '{aa=>1, b=>{2=>1}, c=>{1,2}}', ARRAY['b','c']); + slice +---------------------------- + "b"=>{"2"=>1}, "c"=>[1, 2] +(1 row) + +--to array +SELECT %% 'aa=>1, cq=>l, b=>{a,n}, fg=>NULL'; + ?column? +---------------------------------------- + {b,"[\"a\", \"n\"]",aa,1,cq,l,fg,NULL} +(1 row) + +SELECT %% '{aa,1, cq,l, b,g, fg,NULL}'; + ?column? +------------------------- + {aa,1,cq,l,b,g,fg,NULL} +(1 row) + +SELECT hstore_to_matrix( 'aa=>1, cq=>l, b=>{a,n}, fg=>NULL'); + hstore_to_matrix +------------------------------------------------ + {{b,"[\"a\", \"n\"]"},{aa,1},{cq,l},{fg,NULL}} +(1 row) + +SELECT hstore_to_matrix( '{aa,1, cq,l, b,g, fg,NULL}'); + hstore_to_matrix +--------------------------------- + {{aa,1},{cq,l},{b,g},{fg,NULL}} +(1 row) + +--contains +SELECT 'a=>b'::hstore @> 'a=>b, c=>b'; + ?column? +---------- + f +(1 row) + +SELECT 'a=>b, c=>b'::hstore @> 'a=>b'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{1,2}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{2,1}, c=>b'::hstore @> 'a=>{1,2}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1=>2}, c=>b'::hstore @> 'a=>{1,2}'; + ?column? +---------- + f +(1 row) + +SELECT 'a=>{2=>1}, c=>b'::hstore @> 'a=>{1,2}'; + ?column? +---------- + f +(1 row) + +SELECT 'a=>{1=>2}, c=>b'::hstore @> 'a=>{1=>2}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{2=>1}, c=>b'::hstore @> 'a=>{1=>2}'; + ?column? +---------- + f +(1 row) + +SELECT '{a,b}'::hstore @> '{a,b, c,b}'; + ?column? +---------- + f +(1 row) + +SELECT '{a,b, c,b}'::hstore @> '{a,b}'; + ?column? +---------- + t +(1 row) + +SELECT '{a,b, c,{1,2}}'::hstore @> '{a,{1,2}}'; + ?column? +---------- + t +(1 row) + +SELECT '{a,b, c,{1,2}}'::hstore @> '{b,{1,2}}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{1}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{2}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{3}'; + ?column? +---------- + f +(1 row) + +SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{c=>3}}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4}}'; + ?column? +---------- + t +(1 row) + +SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4},3}'; + ?column? +---------- + f +(1 row) + +SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4},1}'; + ?column? +---------- + t +(1 row) + +-- %> +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'n'; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'a'; + ?column? +---------- + 1 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'b'; + ?column? +---------- + [1, 2] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'c'; + ?column? +---------- + "1"=>2 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'd'; + ?column? +------------- + "1"=>[2, 3] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'd' %> '1'; + ?column? +---------- + [2, 3] +(1 row) + +SELECT '[1,2,3,{a,b}]'::hstore %> '1'; + ?column? +---------- + +(1 row) + +SELECT '["1",2,3,{a,b}]'::hstore %> '1'; + ?column? +---------- + "1" +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 5; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 4; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 3; + ?column? +------------- + "1"=>[2, 3] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 2; + ?column? +---------- + "1"=>2 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 1; + ?column? +---------- + [1, 2] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 0; + ?column? +---------- + 1 +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 5; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 4; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 3; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 2; + ?column? +---------- + "c" +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 1; + ?column? +---------- + "b" +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 0; + ?column? +---------- + "a" +(1 row) + +-- -> +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 5; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 4; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 3; + ?column? +------------- + "1"=>[2, 3] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 2; + ?column? +---------- + "1"=>2 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 1; + ?column? +---------- + [1, 2] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 0; + ?column? +---------- + 1 +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 5; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 4; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 3; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 2; + ?column? +---------- + c +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 1; + ?column? +---------- + b +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 0; + ?column? +---------- + a +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -6; + ?column? +---------- + +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -5; + ?column? +---------- + 1 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -4; + ?column? +---------- + [1, 2] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -3; + ?column? +---------- + "1"=>2 +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -2; + ?column? +------------- + "1"=>[2, 3] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -1; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -6; + ?column? +---------- + +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -5; + ?column? +---------- + a +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -4; + ?column? +---------- + b +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -3; + ?column? +---------- + c +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -2; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -1; + ?column? +---------- + +(1 row) + +-- #> +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{0}'; + ?column? +---------- + +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{a}'; + ?column? +---------- + b +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c}'; + ?column? +----------- + [1, 2, 3] +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 0}'; + ?column? +---------- + 1 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 1}'; + ?column? +---------- + 2 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 2}'; + ?column? +---------- + 3 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 3}'; + ?column? +---------- + +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -1}'; + ?column? +---------- + 3 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -2}'; + ?column? +---------- + 2 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -3}'; + ?column? +---------- + 1 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -4}'; + ?column? +---------- + +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{0}'; + ?column? +---------- + 0 +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{3}'; + ?column? +---------- + [3, 4] +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{4}'; + ?column? +------------- + "5"=>"five" +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{4,5}'; + ?column? +---------- + five +(1 row) + +-- #%> +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{0}'; + ?column? +---------- + +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{a}'; + ?column? +---------- + "b" +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c}'; + ?column? +----------- + [1, 2, 3] +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 0}'; + ?column? +---------- + 1 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 1}'; + ?column? +---------- + 2 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 2}'; + ?column? +---------- + 3 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 3}'; + ?column? +---------- + +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -1}'; + ?column? +---------- + 3 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -2}'; + ?column? +---------- + 2 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -3}'; + ?column? +---------- + 1 +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -4}'; + ?column? +---------- + +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{0}'; + ?column? +---------- + 0 +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{3}'; + ?column? +---------- + [3, 4] +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{4}'; + ?column? +------------- + "5"=>"five" +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{4,5}'; + ?column? +---------- + "five" +(1 row) + +-- ? +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 5; + ?column? +---------- + f +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 4; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 3; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 2; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 1; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 0; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 5; + ?column? +---------- + f +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 4; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 3; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 2; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 1; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 0; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -6; + ?column? +---------- + f +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -5; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -4; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -3; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -2; + ?column? +---------- + t +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -1; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -6; + ?column? +---------- + f +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -5; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -4; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -3; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -2; + ?column? +---------- + t +(1 row) + +SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -1; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{0}'::text[]; + ?column? +---------- + f +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{a}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{b}'::text[]; + ?column? +---------- + f +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 0}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 1}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 2}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 3}'::text[]; + ?column? +---------- + f +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -1}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -2}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -3}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -4}'::text[]; + ?column? +---------- + f +(1 row) + +SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -5}'::text[]; + ?column? +---------- + f +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{0}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{3}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{4}'::text[]; + ?column? +---------- + t +(1 row) + +SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{4,5}'::text[]; + ?column? +---------- + t +(1 row) + +--deep delete +SELECT 'a=>1'::hstore #- '{x}'; + ?column? +---------- + "a"=>1 +(1 row) + +SELECT 'a=>1'::hstore #- '{a}'; + ?column? +---------- + +(1 row) + +SELECT 'a=>1'::hstore #- '{NULL}'; + ?column? +---------- + "a"=>1 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{x}'; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{a}'; + ?column? +---------------- + "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{b}'; + ?column? +---------------- + "a"=>1, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{c}'; + ?column? +---------------- + "a"=>1, "b"=>2 +(1 row) + +SELECT 'a=>1'::hstore #- '{x,1}'; + ?column? +---------- + "a"=>1 +(1 row) + +SELECT 'a=>1'::hstore #- '{a,1}'; + ?column? +---------- + "a"=>1 +(1 row) + +SELECT 'a=>1'::hstore #- '{NULL,1}'; + ?column? +---------- + "a"=>1 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{x,1}'; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{a,1}'; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{b,1}'; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore #- '{c,1}'; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT '[a]'::hstore #- '{2}'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '[a]'::hstore #- '{1}'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '[a]'::hstore #- '{0}'; + ?column? +---------- + +(1 row) + +SELECT '[a]'::hstore #- '{-1}'; + ?column? +---------- + +(1 row) + +SELECT '[a]'::hstore #- '{-2}'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{3}'; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{2}'; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{1}'; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{0}'; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{-1}'; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{-2}'; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{-3}'; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{-4}'; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore #- '{0,0}'; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{x}'; + ?column? +------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{a}'; + ?column? +----------------------------------------------------------- + "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b}'; + ?column? +------------------------------------------------------ + "a"=>1, "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c}'; + ?column? +---------------------------------------------------- + "a"=>1, "b"=>[1, 2], "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d}'; + ?column? +----------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, 0}'; + ?column? +---------------------------------------------------------------- + "a"=>1, "b"=>[2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, -1}'; + ?column? +---------------------------------------------------------------- + "a"=>1, "b"=>[1], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, -1}' #- '{b, -1}'; + ?column? +----------------------------------------------------------------- + "a"=>1, "b"=>NULL, "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c, 1}'; + ?column? +--------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>NULL, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c, 2}'; + ?column? +------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, -2}'; + ?column? +---------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 1}'; + ?column? +---------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}'; + ?column? +---------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[3]}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}' #- '{d, 1, 0}'; + ?column? +----------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>NULL}, "n"=>NULL +(1 row) + +SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}' #- '{d, 1, 0}' #- '{d, 1, 0}'; + ?column? +----------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>NULL}, "n"=>NULL +(1 row) + +-- delete(int) +SELECT '[a,b,c]'::hstore - 3; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore - 2; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '[a,b,c]'::hstore - 1; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore - 0; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore - -1; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '[a,b,c]'::hstore - -2; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore - -3; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '[a,b,c]'::hstore - -4; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - 3; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - 2; + ?column? +---------------- + "a"=>1, "b"=>2 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - 1; + ?column? +---------------- + "a"=>1, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - 0; + ?column? +---------------- + "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - -1; + ?column? +---------------- + "a"=>1, "b"=>2 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - -2; + ?column? +---------------- + "a"=>1, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - -3; + ?column? +---------------- + "b"=>2, "c"=>3 +(1 row) + +SELECT 'a=>1, b=>2, c=>3'::hstore - -4; + ?column? +------------------------ + "a"=>1, "b"=>2, "c"=>3 +(1 row) + +--replace +SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', '{1,2,3}'); + replace +------------------------------------------------------------------------ + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>[1, 2, 3] +(1 row) + +SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{b,-1}', '{1,2,3}'); + replace +--------------------------------------------------------------------------- + "a"=>1, "b"=>[1, [1, 2, 3]], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1,0}', '{1,2,3}'); + replace +--------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[[1, 2, 3], 3]}, "n"=>NULL +(1 row) + +SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,NULL,0}', '{1,2,3}'); + replace +------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +--deep concat +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{}', 'n=>not_null'); + concat_path +------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>"not_null" +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', 'n=>not_null'); + concat_path +-------------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>{"n"=>"not_null"} +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', 'not_null'); + concat_path +------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>"not_null" +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', '{not_null}'); + concat_path +--------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>["not_null"] +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{}', 'b=>{3,4}'); + concat_path +------------------------------------------------------------------- + "a"=>1, "b"=>[3, 4], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{b}', '{3,4}'); + concat_path +------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2, 3, 4], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1}', '{4,5}'); + concat_path +------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3, 4, 5]}, "n"=>NULL +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1}', '4=>5'); + concat_path +--------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3, "4", 5]}, "n"=>NULL +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d}', '2=>{4,5}'); + concat_path +-------------------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3], "2"=>[4, 5]}, "n"=>NULL +(1 row) + +SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{NULL,1}', '4=>5'); + concat_path +------------------------------------------------------------------- + "a"=>1, "b"=>[1, 2], "c"=>{"1"=>2}, "d"=>{"1"=>[2, 3]}, "n"=>NULL +(1 row) + +SELECT concat_path('x'::hstore, '{}'::text[], 'a=>"1"':: hstore); + concat_path +----------------- + ["x", "a", "1"] +(1 row) + +--cast +SELECT ('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::text)::hstore AS err; +ERROR: bad hstore representation +DETAIL: syntax error, unexpected STRING_P, expecting '}' or ',' at end of input +SELECT ('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json)::hstore AS ok; + ok +---------------------------------------------------- + "f2"=>{"f3"=>1}, "f4"=>{"f5"=>99, "f6"=>"stringy"} +(1 row) + +--hvals +SELECT q->'tags' FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore) AS q; + ?column? +---------- + 1 + 3 +(2 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{}') AS q; + q +---------------------------------------------- + [{"sh"=>2, "tags"=>1}, {"sh"=>4, "tags"=>3}] +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3}') AS q; + q +--- +(0 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1}') AS q; + q +-------------------- + "sh"=>4, "tags"=>3 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1}') AS q; + q +-------------------- + "sh"=>4, "tags"=>3 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2}') AS q; + q +--- +(0 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 + "sh"=>4, "tags"=>3 +(2 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3,tags}') AS q; + q +--- +(0 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2,tags}') AS q; + q +--- + 1 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1,tags}') AS q; + q +--- + 3 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0,tags}') AS q; + q +--- + 1 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1,tags}') AS q; + q +--- + 3 +(1 row) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2,tags}') AS q; + q +--- +(0 rows) + +SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL,tags}') AS q; + q +--- + 1 + 3 +(2 rows) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{}') AS q; + q +----------------------------------------------------------------- + "1"=>"first", "a"=>{"b"=>"c", "c"=>"b"}, "b"=>[1, 2], "c"=>"cc" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{1}') AS q; + q +--------- + "first" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a}') AS q; + q +-------------------- + "b"=>"c", "c"=>"b" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b}') AS q; + q +-------- + [1, 2] +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{c}') AS q; + q +------ + "cc" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL}') AS q; + q +-------------------- + "first" + "b"=>"c", "c"=>"b" + [1, 2] + "cc" +(4 rows) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a,c}') AS q; + q +----- + "b" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,c}') AS q; + q +----- + "b" +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b,NULL}') AS q; + q +--- + 1 + 2 +(2 rows) + +SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q; + q +--- + 2 +(1 row) + +SELECT q FROM hvals('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q; + q +--------- + "first" + 2 +(2 rows) + +--svals path +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{}') AS q; + q +---------------------------------------------- + [{"sh"=>2, "tags"=>1}, {"sh"=>4, "tags"=>3}] +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3}') AS q; + q +--- +(0 rows) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1}') AS q; + q +-------------------- + "sh"=>4, "tags"=>3 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1}') AS q; + q +-------------------- + "sh"=>4, "tags"=>3 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2}') AS q; + q +--- +(0 rows) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL}') AS q; + q +-------------------- + "sh"=>2, "tags"=>1 + "sh"=>4, "tags"=>3 +(2 rows) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3,tags}') AS q; + q +--- +(0 rows) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2,tags}') AS q; + q +--- + 1 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1,tags}') AS q; + q +--- + 3 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0,tags}') AS q; + q +--- + 1 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1,tags}') AS q; + q +--- + 3 +(1 row) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2,tags}') AS q; + q +--- +(0 rows) + +SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL,tags}') AS q; + q +--- + 1 + 3 +(2 rows) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{}') AS q; + q +----------------------------------------------------------------- + "1"=>"first", "a"=>{"b"=>"c", "c"=>"b"}, "b"=>[1, 2], "c"=>"cc" +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{1}') AS q; + q +------- + first +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a}') AS q; + q +-------------------- + "b"=>"c", "c"=>"b" +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b}') AS q; + q +-------- + [1, 2] +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{c}') AS q; + q +---- + cc +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL}') AS q; + q +-------------------- + first + "b"=>"c", "c"=>"b" + [1, 2] + cc +(4 rows) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a,c}') AS q; + q +--- + b +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,c}') AS q; + q +--- + b +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b,NULL}') AS q; + q +--- + 1 + 2 +(2 rows) + +SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q; + q +--- + 2 +(1 row) + +SELECT q FROM svals('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q; + q +------- + first + 2 +(2 rows) + +--each +SELECT * FROM each('a=>b, c=>cc'::hstore) AS q; + key | value +-----+------- + a | b + c | cc +(2 rows) + +SELECT * FROM each('[a, b, c, cc]'::hstore) AS q; + key | value +-----+------- + | a + | b + | c + | cc +(4 rows) + +SELECT * FROM each('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first, n=>null'::hstore) AS q; + key | value +-----+---------------------------------- + 1 | first + a | "1"=>"first", "b"=>"c", "c"=>"b" + b | [1, 2] + c | cc + n | +(5 rows) + +SELECT * FROM each_hstore('a=>b, c=>cc'::hstore) AS q; + key | value +-----+------- + a | "b" + c | "cc" +(2 rows) + +SELECT * FROM each_hstore('[a, b, c, cc]'::hstore) AS q; + key | value +-----+------- + | "a" + | "b" + | "c" + | "cc" +(4 rows) + +SELECT * FROM each_hstore('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first, n=>null'::hstore) AS q; + key | value +-----+---------------------------------- + 1 | "first" + a | "1"=>"first", "b"=>"c", "c"=>"b" + b | [1, 2] + c | "cc" + n | +(5 rows) + +--decoration +SELECT 'a=>1, b=>{c=>3}, d=>[4,[5]]'::hstore AS h, '[a, {b=>c}, [c, d, e]]'::hstore AS a; + h | a +--------------------------------------+------------------------------------ + "a"=>1, "b"=>{"c"=>3}, "d"=>[4, [5]] | ["a", {"b"=>"c"}, ["c", "d", "e"]] +(1 row) + +SET hstore.pretty_print = true; +SELECT 'a=>1, b=>{c=>3}, d=>[4,[5]], e=>[1,2,3,4], f=>g, g=>j'::hstore AS h, + '[a, {b=>c, c=>d}, [c, d, e, [1,2], h, {f=>g, g=>f}]]'::hstore AS a; + h | a +------------+------------------------ + "a"=>1, +| [ + + "b"=> +| "a", + + { +| { + + "c"=>3+| "b"=>"c", + + }, +| "c"=>"d" + + "d"=> +| }, + + [ +| [ + + 4, +| "c", + + [ +| "d", + + 5 +| "e", + + ] +| [ + + ], +| 1, + + "e"=> +| 2 + + [ +| ], + + 1, +| "h", + + 2, +| { + + 3, +| "f"=>"g", + + 4 +| "g"=>f + + ], +| } + + "f"=>"g", +| ] + + "g"=>"j" | ] +(1 row) + +RESET hstore.pretty_print; +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore); + hstore_print +----------------------------------------------------------------------- + "a"=>t, "f"=>t, "t"=>"f", "123"=>"string", "arr"=>[1, 2, 3, "3", "x"] +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true ); + hstore_print +------------------------------------------------------------------- + "a"=>t, "f"=>t, "t"=>f, "123"=>"string", "arr"=>[1, 2, 3, 3, "x"] +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true ); + hstore_print +------------------------------------------------------------------------- + {"a"=>t, "f"=>t, "t"=>"f", "123"=>"string", "arr"=>[1, 2, 3, "3", "x"]} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, array_curly_braces := true ); + hstore_print +----------------------------------------------------------------------- + "a"=>t, "f"=>t, "t"=>"f", "123"=>"string", "arr"=>{1, 2, 3, "3", "x"} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true ); + hstore_print +----------------------------------------------------------------------------- + "a": true, "f": true, "t": "f", "123": "string", "arr": [1, 2, 3, "3", "x"] +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, loose := true ); + hstore_print +----------------------------------------------------------------------------- + "a": true, "f": true, "t": false, "123": "string", "arr": [1, 2, 3, 3, "x"] +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, root_hash_decorated := true ); + hstore_print +------------------------------------------------------------------------------- + {"a": true, "f": true, "t": "f", "123": "string", "arr": [1, 2, 3, "3", "x"]} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, array_curly_braces := true ); + hstore_print +----------------------------------------------------------------------------- + "a": true, "f": true, "t": "f", "123": "string", "arr": {1, 2, 3, "3", "x"} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true, root_hash_decorated := true ); + hstore_print +--------------------------------------------------------------------- + {"a"=>t, "f"=>t, "t"=>f, "123"=>"string", "arr"=>[1, 2, 3, 3, "x"]} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true, array_curly_braces := true ); + hstore_print +------------------------------------------------------------------- + "a"=>t, "f"=>t, "t"=>f, "123"=>"string", "arr"=>{1, 2, 3, 3, "x"} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true, array_curly_braces := true ); + hstore_print +------------------------------------------------------------------------- + {"a"=>t, "f"=>t, "t"=>"f", "123"=>"string", "arr"=>{1, 2, 3, "3", "x"}} +(1 row) + +SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true, array_curly_braces := true, loose := true); + hstore_print +--------------------------------------------------------------------- + {"a"=>t, "f"=>t, "t"=>f, "123"=>"string", "arr"=>{1, 2, 3, 3, "x"}} +(1 row) +