diff options
Diffstat (limited to 'contrib/hstore')
| -rw-r--r-- | contrib/hstore/Makefile | 3 | ||||
| -rw-r--r-- | contrib/hstore/expected/hstore.out | 20 | ||||
| -rw-r--r-- | contrib/hstore/hstore--1.2--1.3.sql | 17 | ||||
| -rw-r--r-- | contrib/hstore/hstore--1.3.sql (renamed from contrib/hstore/hstore--1.2.sql) | 15 | ||||
| -rw-r--r-- | contrib/hstore/hstore.control | 2 | ||||
| -rw-r--r-- | contrib/hstore/hstore_io.c | 165 | ||||
| -rw-r--r-- | contrib/hstore/sql/hstore.sql | 6 |
7 files changed, 223 insertions, 5 deletions
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.3.sql index f415a7230b..995ade1b3c 100644 --- a/contrib/hstore/hstore--1.2.sql +++ b/contrib/hstore/hstore--1.3.sql @@ -1,4 +1,4 @@ -/* contrib/hstore/hstore--1.1.sql */ +/* 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 @@ -247,6 +247,19 @@ 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' 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'); |