From d9134d0a355cfa447adc80db4505d5931084278a Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Sun, 23 Mar 2014 16:40:19 -0400 Subject: Introduce jsonb, a structured format for storing json. The new format accepts exactly the same data as the json type. However, it is stored in a format that does not require reparsing the orgiginal text in order to process it, making it much more suitable for indexing and other operations. Insignificant whitespace is discarded, and the order of object keys is not preserved. Neither are duplicate object keys kept - the later value for a given key is the only one stored. The new type has all the functions and operators that the json type has, with the exception of the json generation functions (to_json, json_agg etc.) and with identical semantics. In addition, there are operator classes for hash and btree indexing, and two classes for GIN indexing, that have no equivalent in the json type. This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which was intended to provide similar facilities to a nested hstore type, but which in the end proved to have some significant compatibility issues. Authors: Oleg Bartunov, Teodor Sigaev, Peter Geoghegan and Andrew Dunstan. Review: Andres Freund --- contrib/hstore/Makefile | 3 +- contrib/hstore/expected/hstore.out | 20 +- contrib/hstore/hstore--1.2--1.3.sql | 17 ++ contrib/hstore/hstore--1.2.sql | 537 ----------------------------------- contrib/hstore/hstore--1.3.sql | 550 ++++++++++++++++++++++++++++++++++++ contrib/hstore/hstore.control | 2 +- contrib/hstore/hstore_io.c | 165 +++++++++++ contrib/hstore/sql/hstore.sql | 6 +- 8 files changed, 759 insertions(+), 541 deletions(-) create mode 100644 contrib/hstore/hstore--1.2--1.3.sql delete mode 100644 contrib/hstore/hstore--1.2.sql create mode 100644 contrib/hstore/hstore--1.3.sql (limited to 'contrib/hstore') diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile index 43b7e5f9e7..2b60fbed0e 100644 --- a/contrib/hstore/Makefile +++ b/contrib/hstore/Makefile @@ -5,7 +5,8 @@ OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \ crc32.o EXTENSION = hstore -DATA = hstore--1.2.sql hstore--1.1--1.2.sql hstore--1.0--1.1.sql \ +DATA = hstore--1.3.sql hstore--1.2--1.3.sql \ + hstore--1.1--1.2.sql hstore--1.0--1.1.sql \ hstore--unpackaged--1.0.sql REGRESS = hstore diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out index 2114143022..9749e45c14 100644 --- a/contrib/hstore/expected/hstore.out +++ b/contrib/hstore/expected/hstore.out @@ -1453,7 +1453,7 @@ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe 1 (1 row) --- json +-- json and jsonb select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); hstore_to_json ------------------------------------------------------------------------------------------------- @@ -1472,6 +1472,24 @@ select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012 {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1} (1 row) +select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); + hstore_to_jsonb +------------------------------------------------------------------------------------------------- + {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} +(1 row) + +select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb); + jsonb +------------------------------------------------------------------------------------------------- + {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} +(1 row) + +select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); + hstore_to_jsonb_loose +--------------------------------------------------------------------------------------- + {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1} +(1 row) + create table test_json_agg (f1 text, f2 hstore); insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'), ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4'); diff --git a/contrib/hstore/hstore--1.2--1.3.sql b/contrib/hstore/hstore--1.2--1.3.sql new file mode 100644 index 0000000000..0a7056015b --- /dev/null +++ b/contrib/hstore/hstore--1.2--1.3.sql @@ -0,0 +1,17 @@ +/* contrib/hstore/hstore--1.2--1.3.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION hstore UPDATE TO '1.3'" to load this file. \quit + +CREATE FUNCTION hstore_to_jsonb(hstore) +RETURNS jsonb +AS 'MODULE_PATHNAME', 'hstore_to_jsonb' +LANGUAGE C IMMUTABLE STRICT; + +CREATE CAST (hstore AS jsonb) + WITH FUNCTION hstore_to_jsonb(hstore); + +CREATE FUNCTION hstore_to_jsonb_loose(hstore) +RETURNS jsonb +AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose' +LANGUAGE C IMMUTABLE STRICT; diff --git a/contrib/hstore/hstore--1.2.sql b/contrib/hstore/hstore--1.2.sql deleted file mode 100644 index f415a7230b..0000000000 --- a/contrib/hstore/hstore--1.2.sql +++ /dev/null @@ -1,537 +0,0 @@ -/* contrib/hstore/hstore--1.1.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION hstore" to load this file. \quit - -CREATE TYPE hstore; - -CREATE FUNCTION hstore_in(cstring) -RETURNS hstore -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_out(hstore) -RETURNS cstring -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_recv(internal) -RETURNS hstore -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_send(hstore) -RETURNS bytea -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE TYPE hstore ( - INTERNALLENGTH = -1, - INPUT = hstore_in, - OUTPUT = hstore_out, - RECEIVE = hstore_recv, - SEND = hstore_send, - STORAGE = extended -); - -CREATE FUNCTION hstore_version_diag(hstore) -RETURNS integer -AS 'MODULE_PATHNAME','hstore_version_diag' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION fetchval(hstore,text) -RETURNS text -AS 'MODULE_PATHNAME','hstore_fetchval' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR -> ( - LEFTARG = hstore, - RIGHTARG = text, - PROCEDURE = fetchval -); - -CREATE FUNCTION slice_array(hstore,text[]) -RETURNS text[] -AS 'MODULE_PATHNAME','hstore_slice_to_array' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR -> ( - LEFTARG = hstore, - RIGHTARG = text[], - PROCEDURE = slice_array -); - -CREATE FUNCTION slice(hstore,text[]) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_slice_to_hstore' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION isexists(hstore,text) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_exists' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION exist(hstore,text) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_exists' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR ? ( - LEFTARG = hstore, - RIGHTARG = text, - PROCEDURE = exist, - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE FUNCTION exists_any(hstore,text[]) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_exists_any' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR ?| ( - LEFTARG = hstore, - RIGHTARG = text[], - PROCEDURE = exists_any, - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE FUNCTION exists_all(hstore,text[]) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_exists_all' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR ?& ( - LEFTARG = hstore, - RIGHTARG = text[], - PROCEDURE = exists_all, - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE FUNCTION isdefined(hstore,text) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_defined' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION defined(hstore,text) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_defined' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION delete(hstore,text) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_delete' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION delete(hstore,text[]) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_delete_array' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION delete(hstore,hstore) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_delete_hstore' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR - ( - LEFTARG = hstore, - RIGHTARG = text, - PROCEDURE = delete -); - -CREATE OPERATOR - ( - LEFTARG = hstore, - RIGHTARG = text[], - PROCEDURE = delete -); - -CREATE OPERATOR - ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = delete -); - -CREATE FUNCTION hs_concat(hstore,hstore) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_concat' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR || ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hs_concat -); - -CREATE FUNCTION hs_contains(hstore,hstore) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_contains' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hs_contained(hstore,hstore) -RETURNS bool -AS 'MODULE_PATHNAME','hstore_contained' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR @> ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hs_contains, - COMMUTATOR = '<@', - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE OPERATOR <@ ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hs_contained, - COMMUTATOR = '@>', - RESTRICT = contsel, - JOIN = contjoinsel -); - --- obsolete: -CREATE OPERATOR @ ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hs_contains, - COMMUTATOR = '~', - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE OPERATOR ~ ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hs_contained, - COMMUTATOR = '@', - RESTRICT = contsel, - JOIN = contjoinsel -); - -CREATE FUNCTION tconvert(text,text) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_from_text' -LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) - -CREATE FUNCTION hstore(text,text) -RETURNS hstore -AS 'MODULE_PATHNAME','hstore_from_text' -LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) - -CREATE FUNCTION hstore(text[],text[]) -RETURNS hstore -AS 'MODULE_PATHNAME', 'hstore_from_arrays' -LANGUAGE C IMMUTABLE; -- not STRICT; allows (keys,null) - -CREATE FUNCTION hstore(text[]) -RETURNS hstore -AS 'MODULE_PATHNAME', 'hstore_from_array' -LANGUAGE C IMMUTABLE STRICT; - -CREATE CAST (text[] AS hstore) - WITH FUNCTION hstore(text[]); - -CREATE FUNCTION hstore_to_json(hstore) -RETURNS json -AS 'MODULE_PATHNAME', 'hstore_to_json' -LANGUAGE C IMMUTABLE STRICT; - -CREATE CAST (hstore AS json) - WITH FUNCTION hstore_to_json(hstore); - -CREATE FUNCTION hstore_to_json_loose(hstore) -RETURNS json -AS 'MODULE_PATHNAME', 'hstore_to_json_loose' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION hstore(record) -RETURNS hstore -AS 'MODULE_PATHNAME', 'hstore_from_record' -LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::recordtype) - -CREATE FUNCTION hstore_to_array(hstore) -RETURNS text[] -AS 'MODULE_PATHNAME','hstore_to_array' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR %% ( - RIGHTARG = hstore, - PROCEDURE = hstore_to_array -); - -CREATE FUNCTION hstore_to_matrix(hstore) -RETURNS text[] -AS 'MODULE_PATHNAME','hstore_to_matrix' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR %# ( - RIGHTARG = hstore, - PROCEDURE = hstore_to_matrix -); - -CREATE FUNCTION akeys(hstore) -RETURNS text[] -AS 'MODULE_PATHNAME','hstore_akeys' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION avals(hstore) -RETURNS text[] -AS 'MODULE_PATHNAME','hstore_avals' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION skeys(hstore) -RETURNS setof text -AS 'MODULE_PATHNAME','hstore_skeys' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION svals(hstore) -RETURNS setof text -AS 'MODULE_PATHNAME','hstore_svals' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION each(IN hs hstore, - OUT key text, - OUT value text) -RETURNS SETOF record -AS 'MODULE_PATHNAME','hstore_each' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION populate_record(anyelement,hstore) -RETURNS anyelement -AS 'MODULE_PATHNAME', 'hstore_populate_record' -LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::rectype,hstore) - -CREATE OPERATOR #= ( - LEFTARG = anyelement, - RIGHTARG = hstore, - PROCEDURE = populate_record -); - --- btree support - -CREATE FUNCTION hstore_eq(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_eq' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_ne(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_ne' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_gt(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_gt' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_ge(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_ge' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_lt(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_lt' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_le(hstore,hstore) -RETURNS boolean -AS 'MODULE_PATHNAME','hstore_le' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION hstore_cmp(hstore,hstore) -RETURNS integer -AS 'MODULE_PATHNAME','hstore_cmp' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR = ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_eq, - COMMUTATOR = =, - NEGATOR = <>, - RESTRICT = eqsel, - JOIN = eqjoinsel, - MERGES, - HASHES -); -CREATE OPERATOR <> ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_ne, - COMMUTATOR = <>, - NEGATOR = =, - RESTRICT = neqsel, - JOIN = neqjoinsel -); - --- the comparison operators have funky names (and are undocumented) --- in an attempt to discourage anyone from actually using them. they --- only exist to support the btree opclass - -CREATE OPERATOR #<# ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_lt, - COMMUTATOR = #>#, - NEGATOR = #>=#, - RESTRICT = scalarltsel, - JOIN = scalarltjoinsel -); -CREATE OPERATOR #<=# ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_le, - COMMUTATOR = #>=#, - NEGATOR = #>#, - RESTRICT = scalarltsel, - JOIN = scalarltjoinsel -); -CREATE OPERATOR #># ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_gt, - COMMUTATOR = #<#, - NEGATOR = #<=#, - RESTRICT = scalargtsel, - JOIN = scalargtjoinsel -); -CREATE OPERATOR #>=# ( - LEFTARG = hstore, - RIGHTARG = hstore, - PROCEDURE = hstore_ge, - COMMUTATOR = #<=#, - NEGATOR = #<#, - RESTRICT = scalargtsel, - JOIN = scalargtjoinsel -); - -CREATE OPERATOR CLASS btree_hstore_ops -DEFAULT FOR TYPE hstore USING btree -AS - OPERATOR 1 #<# , - OPERATOR 2 #<=# , - OPERATOR 3 = , - OPERATOR 4 #>=# , - OPERATOR 5 #># , - FUNCTION 1 hstore_cmp(hstore,hstore); - --- hash support - -CREATE FUNCTION hstore_hash(hstore) -RETURNS integer -AS 'MODULE_PATHNAME','hstore_hash' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS hash_hstore_ops -DEFAULT FOR TYPE hstore USING hash -AS - OPERATOR 1 = , - FUNCTION 1 hstore_hash(hstore); - --- GiST support - -CREATE TYPE ghstore; - -CREATE FUNCTION ghstore_in(cstring) -RETURNS ghstore -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION ghstore_out(ghstore) -RETURNS cstring -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE TYPE ghstore ( - INTERNALLENGTH = -1, - INPUT = ghstore_in, - OUTPUT = ghstore_out -); - -CREATE FUNCTION ghstore_compress(internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_decompress(internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_penalty(internal,internal,internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_picksplit(internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_union(internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_same(internal, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION ghstore_consistent(internal,internal,int,oid,internal) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE OPERATOR CLASS gist_hstore_ops -DEFAULT FOR TYPE hstore USING gist -AS - OPERATOR 7 @> , - OPERATOR 9 ?(hstore,text) , - OPERATOR 10 ?|(hstore,text[]) , - OPERATOR 11 ?&(hstore,text[]) , - --OPERATOR 8 <@ , - OPERATOR 13 @ , - --OPERATOR 14 ~ , - FUNCTION 1 ghstore_consistent (internal, internal, int, oid, internal), - FUNCTION 2 ghstore_union (internal, internal), - FUNCTION 3 ghstore_compress (internal), - FUNCTION 4 ghstore_decompress (internal), - FUNCTION 5 ghstore_penalty (internal, internal, internal), - FUNCTION 6 ghstore_picksplit (internal, internal), - FUNCTION 7 ghstore_same (internal, internal, internal), - STORAGE ghstore; - --- GIN support - -CREATE FUNCTION gin_extract_hstore(internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION gin_extract_hstore_query(internal, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE STRICT; - -CREATE OPERATOR CLASS gin_hstore_ops -DEFAULT FOR TYPE hstore USING gin -AS - OPERATOR 7 @>, - OPERATOR 9 ?(hstore,text), - OPERATOR 10 ?|(hstore,text[]), - OPERATOR 11 ?&(hstore,text[]), - FUNCTION 1 bttextcmp(text,text), - FUNCTION 2 gin_extract_hstore(internal, internal), - FUNCTION 3 gin_extract_hstore_query(internal, internal, int2, internal, internal), - FUNCTION 4 gin_consistent_hstore(internal, int2, internal, int4, internal, internal), - STORAGE text; diff --git a/contrib/hstore/hstore--1.3.sql b/contrib/hstore/hstore--1.3.sql new file mode 100644 index 0000000000..995ade1b3c --- /dev/null +++ b/contrib/hstore/hstore--1.3.sql @@ -0,0 +1,550 @@ +/* contrib/hstore/hstore--1.3.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION hstore" to load this file. \quit + +CREATE TYPE hstore; + +CREATE FUNCTION hstore_in(cstring) +RETURNS hstore +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_out(hstore) +RETURNS cstring +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_recv(internal) +RETURNS hstore +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_send(hstore) +RETURNS bytea +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE TYPE hstore ( + INTERNALLENGTH = -1, + INPUT = hstore_in, + OUTPUT = hstore_out, + RECEIVE = hstore_recv, + SEND = hstore_send, + STORAGE = extended +); + +CREATE FUNCTION hstore_version_diag(hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_version_diag' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION fetchval(hstore,text) +RETURNS text +AS 'MODULE_PATHNAME','hstore_fetchval' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR -> ( + LEFTARG = hstore, + RIGHTARG = text, + PROCEDURE = fetchval +); + +CREATE FUNCTION slice_array(hstore,text[]) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_slice_to_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR -> ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = slice_array +); + +CREATE FUNCTION slice(hstore,text[]) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_slice_to_hstore' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION isexists(hstore,text) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION exist(hstore,text) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR ? ( + LEFTARG = hstore, + RIGHTARG = text, + PROCEDURE = exist, + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION exists_any(hstore,text[]) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists_any' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR ?| ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = exists_any, + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION exists_all(hstore,text[]) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists_all' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR ?& ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = exists_all, + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION isdefined(hstore,text) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_defined' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION defined(hstore,text) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_defined' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION delete(hstore,text) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_delete' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION delete(hstore,text[]) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_delete_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION delete(hstore,hstore) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_delete_hstore' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = text, + PROCEDURE = delete +); + +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = delete +); + +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = delete +); + +CREATE FUNCTION hs_concat(hstore,hstore) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_concat' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR || ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hs_concat +); + +CREATE FUNCTION hs_contains(hstore,hstore) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_contains' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hs_contained(hstore,hstore) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_contained' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR @> ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hs_contains, + COMMUTATOR = '<@', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE OPERATOR <@ ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hs_contained, + COMMUTATOR = '@>', + RESTRICT = contsel, + JOIN = contjoinsel +); + +-- obsolete: +CREATE OPERATOR @ ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hs_contains, + COMMUTATOR = '~', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE OPERATOR ~ ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hs_contained, + COMMUTATOR = '@', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION tconvert(text,text) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_from_text' +LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) + +CREATE FUNCTION hstore(text,text) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_from_text' +LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) + +CREATE FUNCTION hstore(text[],text[]) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_arrays' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (keys,null) + +CREATE FUNCTION hstore(text[]) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_array' +LANGUAGE C IMMUTABLE STRICT; + +CREATE CAST (text[] AS hstore) + WITH FUNCTION hstore(text[]); + +CREATE FUNCTION hstore_to_json(hstore) +RETURNS json +AS 'MODULE_PATHNAME', 'hstore_to_json' +LANGUAGE C IMMUTABLE STRICT; + +CREATE CAST (hstore AS json) + WITH FUNCTION hstore_to_json(hstore); + +CREATE FUNCTION hstore_to_json_loose(hstore) +RETURNS json +AS 'MODULE_PATHNAME', 'hstore_to_json_loose' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION hstore_to_jsonb(hstore) +RETURNS jsonb +AS 'MODULE_PATHNAME', 'hstore_to_jsonb' +LANGUAGE C IMMUTABLE STRICT; + +CREATE CAST (hstore AS jsonb) + WITH FUNCTION hstore_to_jsonb(hstore); + +CREATE FUNCTION hstore_to_jsonb_loose(hstore) +RETURNS jsonb +AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION hstore(record) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_record' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::recordtype) + +CREATE FUNCTION hstore_to_array(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_to_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR %% ( + RIGHTARG = hstore, + PROCEDURE = hstore_to_array +); + +CREATE FUNCTION hstore_to_matrix(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_to_matrix' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR %# ( + RIGHTARG = hstore, + PROCEDURE = hstore_to_matrix +); + +CREATE FUNCTION akeys(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_akeys' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION avals(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_avals' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION skeys(hstore) +RETURNS setof text +AS 'MODULE_PATHNAME','hstore_skeys' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION svals(hstore) +RETURNS setof text +AS 'MODULE_PATHNAME','hstore_svals' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION each(IN hs hstore, + OUT key text, + OUT value text) +RETURNS SETOF record +AS 'MODULE_PATHNAME','hstore_each' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION populate_record(anyelement,hstore) +RETURNS anyelement +AS 'MODULE_PATHNAME', 'hstore_populate_record' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::rectype,hstore) + +CREATE OPERATOR #= ( + LEFTARG = anyelement, + RIGHTARG = hstore, + PROCEDURE = populate_record +); + +-- btree support + +CREATE FUNCTION hstore_eq(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_eq' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_ne(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_ne' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_gt(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_gt' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_ge(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_ge' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_lt(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_lt' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_le(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_le' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION hstore_cmp(hstore,hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_cmp' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR = ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_eq, + COMMUTATOR = =, + NEGATOR = <>, + RESTRICT = eqsel, + JOIN = eqjoinsel, + MERGES, + HASHES +); +CREATE OPERATOR <> ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_ne, + COMMUTATOR = <>, + NEGATOR = =, + RESTRICT = neqsel, + JOIN = neqjoinsel +); + +-- the comparison operators have funky names (and are undocumented) +-- in an attempt to discourage anyone from actually using them. they +-- only exist to support the btree opclass + +CREATE OPERATOR #<# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_lt, + COMMUTATOR = #>#, + NEGATOR = #>=#, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel +); +CREATE OPERATOR #<=# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_le, + COMMUTATOR = #>=#, + NEGATOR = #>#, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel +); +CREATE OPERATOR #># ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_gt, + COMMUTATOR = #<#, + NEGATOR = #<=#, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel +); +CREATE OPERATOR #>=# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_ge, + COMMUTATOR = #<=#, + NEGATOR = #<#, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel +); + +CREATE OPERATOR CLASS btree_hstore_ops +DEFAULT FOR TYPE hstore USING btree +AS + OPERATOR 1 #<# , + OPERATOR 2 #<=# , + OPERATOR 3 = , + OPERATOR 4 #>=# , + OPERATOR 5 #># , + FUNCTION 1 hstore_cmp(hstore,hstore); + +-- hash support + +CREATE FUNCTION hstore_hash(hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_hash' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS hash_hstore_ops +DEFAULT FOR TYPE hstore USING hash +AS + OPERATOR 1 = , + FUNCTION 1 hstore_hash(hstore); + +-- GiST support + +CREATE TYPE ghstore; + +CREATE FUNCTION ghstore_in(cstring) +RETURNS ghstore +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION ghstore_out(ghstore) +RETURNS cstring +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE TYPE ghstore ( + INTERNALLENGTH = -1, + INPUT = ghstore_in, + OUTPUT = ghstore_out +); + +CREATE FUNCTION ghstore_compress(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_decompress(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_penalty(internal,internal,internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_picksplit(internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_union(internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_same(internal, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION ghstore_consistent(internal,internal,int,oid,internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR CLASS gist_hstore_ops +DEFAULT FOR TYPE hstore USING gist +AS + OPERATOR 7 @> , + OPERATOR 9 ?(hstore,text) , + OPERATOR 10 ?|(hstore,text[]) , + OPERATOR 11 ?&(hstore,text[]) , + --OPERATOR 8 <@ , + OPERATOR 13 @ , + --OPERATOR 14 ~ , + FUNCTION 1 ghstore_consistent (internal, internal, int, oid, internal), + FUNCTION 2 ghstore_union (internal, internal), + FUNCTION 3 ghstore_compress (internal), + FUNCTION 4 ghstore_decompress (internal), + FUNCTION 5 ghstore_penalty (internal, internal, internal), + FUNCTION 6 ghstore_picksplit (internal, internal), + FUNCTION 7 ghstore_same (internal, internal, internal), + STORAGE ghstore; + +-- GIN support + +CREATE FUNCTION gin_extract_hstore(internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gin_extract_hstore_query(internal, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR CLASS gin_hstore_ops +DEFAULT FOR TYPE hstore USING gin +AS + OPERATOR 7 @>, + OPERATOR 9 ?(hstore,text), + OPERATOR 10 ?|(hstore,text[]), + OPERATOR 11 ?&(hstore,text[]), + FUNCTION 1 bttextcmp(text,text), + FUNCTION 2 gin_extract_hstore(internal, internal), + FUNCTION 3 gin_extract_hstore_query(internal, internal, int2, internal, internal), + FUNCTION 4 gin_consistent_hstore(internal, int2, internal, int4, internal, internal), + STORAGE text; diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control index 9daf5e2e00..dcc3b687cf 100644 --- a/contrib/hstore/hstore.control +++ b/contrib/hstore/hstore.control @@ -1,5 +1,5 @@ # hstore extension comment = 'data type for storing sets of (key, value) pairs' -default_version = '1.2' +default_version = '1.3' module_pathname = '$libdir/hstore' relocatable = true diff --git a/contrib/hstore/hstore_io.c b/contrib/hstore/hstore_io.c index 65e4438d32..6cfc935788 100644 --- a/contrib/hstore/hstore_io.c +++ b/contrib/hstore/hstore_io.c @@ -12,6 +12,7 @@ #include "libpq/pqformat.h" #include "utils/builtins.h" #include "utils/json.h" +#include "utils/jsonb.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/typcache.h" @@ -1374,3 +1375,167 @@ hstore_to_json(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(dst.data)); } + +PG_FUNCTION_INFO_V1(hstore_to_jsonb); +Datum hstore_to_jsonb(PG_FUNCTION_ARGS); +Datum +hstore_to_jsonb(PG_FUNCTION_ARGS) +{ + HStore *in = PG_GETARG_HS(0); + int i; + int count = HS_COUNT(in); + char *base = STRPTR(in); + HEntry *entries = ARRPTR(in); + JsonbParseState *state = NULL; + JsonbValue *res; + + res = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL); + + for (i = 0; i < count; i++) + { + JsonbValue key, val; + + key.estSize = sizeof(JEntry); + key.type = jbvString; + key.string.len = HS_KEYLEN(entries, i); + key.string.val = pnstrdup(HS_KEY(entries, base, i), key.string.len); + key.estSize += key.string.len; + + res = pushJsonbValue(&state, WJB_KEY, &key); + + if (HS_VALISNULL(entries, i)) + { + val.estSize = sizeof(JEntry); + val.type = jbvNull; + } + else + { + val.estSize = sizeof(JEntry); + val.type = jbvString; + val.string.len = HS_VALLEN(entries, i); + val.string.val = pnstrdup(HS_VAL(entries, base, i), val.string.len); + val.estSize += val.string.len; + } + res = pushJsonbValue(&state, WJB_VALUE, &val); + } + + res = pushJsonbValue(&state, WJB_END_OBJECT, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(res)); +} + +PG_FUNCTION_INFO_V1(hstore_to_jsonb_loose); +Datum hstore_to_jsonb_loose(PG_FUNCTION_ARGS); +Datum +hstore_to_jsonb_loose(PG_FUNCTION_ARGS) +{ + HStore *in = PG_GETARG_HS(0); + int i; + int count = HS_COUNT(in); + char *base = STRPTR(in); + HEntry *entries = ARRPTR(in); + JsonbParseState *state = NULL; + JsonbValue *res; + StringInfoData tmp; + bool is_number; + + initStringInfo(&tmp); + + res = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL); + + for (i = 0; i < count; i++) + { + JsonbValue key, val; + + key.estSize = sizeof(JEntry); + key.type = jbvString; + key.string.len = HS_KEYLEN(entries, i); + key.string.val = pnstrdup(HS_KEY(entries, base, i), key.string.len); + key.estSize += key.string.len; + + res = pushJsonbValue(&state, WJB_KEY, &key); + + val.estSize = sizeof(JEntry); + + if (HS_VALISNULL(entries, i)) + { + val.type = jbvNull; + } + /* guess that values of 't' or 'f' are booleans */ + else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 't') + { + val.type = jbvBool; + val.boolean = true; + } + else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 'f') + { + val.type = jbvBool; + val.boolean = false; + } + else + { + is_number = false; + resetStringInfo(&tmp); + + appendBinaryStringInfo(&tmp, HS_VAL(entries, base, i), HS_VALLEN(entries, i)); + + /* + * don't treat something with a leading zero followed by another + * digit as numeric - could be a zip code or similar + */ + if (tmp.len > 0 && + !(tmp.data[0] == '0' && + isdigit((unsigned char) tmp.data[1])) && + strspn(tmp.data, "+-0123456789Ee.") == tmp.len) + { + /* + * might be a number. See if we can input it as a numeric + * value. Ignore any actual parsed value. + */ + char *endptr = "junk"; + long lval; + + lval = strtol(tmp.data, &endptr, 10); + (void) lval; + if (*endptr == '\0') + { + /* + * strol man page says this means the whole string is + * valid + */ + is_number = true; + } + else + { + /* not an int - try a double */ + double dval; + + dval = strtod(tmp.data, &endptr); + (void) dval; + if (*endptr == '\0') + is_number = true; + } + } + if (is_number) + { + val.type = jbvNumeric; + val.numeric = DatumGetNumeric( + DirectFunctionCall3(numeric_in, CStringGetDatum(tmp.data), 0, -1)); + val.estSize += VARSIZE_ANY(val.numeric) +sizeof(JEntry); + } + else + { + val.estSize = sizeof(JEntry); + val.type = jbvString; + val.string.len = HS_VALLEN(entries, i); + val.string.val = pnstrdup(HS_VAL(entries, base, i), val.string.len); + val.estSize += val.string.len; + } + } + res = pushJsonbValue(&state, WJB_VALUE, &val); + } + + res = pushJsonbValue(&state, WJB_END_OBJECT, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(res)); +} diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql index 9518f569ec..5a9e9ee5ae 100644 --- a/contrib/hstore/sql/hstore.sql +++ b/contrib/hstore/sql/hstore.sql @@ -331,11 +331,15 @@ set enable_seqscan=off; select count(*) from testhstore where h #># 'p=>1'; select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t'; --- json +-- json and jsonb select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json); select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); +select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); +select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb); +select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); + create table test_json_agg (f1 text, f2 hstore); insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'), ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4'); -- cgit v1.2.1