Created
April 4, 2013 02:05
-
-
Save luzluna/5307140 to your computer and use it in GitHub Desktop.
Postgres 9.2.3 hstore patch for hstore_to_json(), hstore_to_json_loose() function. backport from 9.3 development branch.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
diff -rupN hstore/hstore--1.1.sql hstore_hstore_to_json/hstore--1.1.sql | |
--- hstore/hstore--1.1.sql 2013-02-05 06:28:13.000000000 +0900 | |
+++ hstore_hstore_to_json/hstore--1.1.sql 2013-04-04 10:53:53.000000000 +0900 | |
@@ -234,6 +234,19 @@ 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' | |
diff -rupN hstore/hstore_io.c hstore_hstore_to_json/hstore_io.c | |
--- hstore/hstore_io.c 2013-02-05 06:28:13.000000000 +0900 | |
+++ hstore_hstore_to_json/hstore_io.c 2013-04-04 10:53:53.000000000 +0900 | |
@@ -7,7 +7,10 @@ | |
#include "catalog/pg_type.h" | |
#include "funcapi.h" | |
+#include "lib/stringinfo.h" | |
#include "libpq/pqformat.h" | |
+#include "utils/builtins.h" | |
+#include "utils/json.h" | |
#include "utils/lsyscache.h" | |
#include "utils/typcache.h" | |
@@ -1210,3 +1213,223 @@ hstore_send(PG_FUNCTION_ARGS) | |
PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); | |
} | |
+ | |
+ | |
+/* | |
+ * hstore_to_json_loose | |
+ * | |
+ * This is a heuristic conversion to json which treats | |
+ * 't' and 'f' as booleans and strings that look like numbers as numbers, | |
+ * as long as they don't start with a leading zero followed by another digit | |
+ * (think zip codes or phone numbers starting with 0). | |
+ */ | |
+PG_FUNCTION_INFO_V1(hstore_to_json_loose); | |
+Datum hstore_to_json_loose(PG_FUNCTION_ARGS); | |
+Datum | |
+hstore_to_json_loose(PG_FUNCTION_ARGS) | |
+{ | |
+ HStore *in = PG_GETARG_HS(0); | |
+ int buflen, | |
+ i; | |
+ int count = HS_COUNT(in); | |
+ char *out, | |
+ *ptr; | |
+ char *base = STRPTR(in); | |
+ HEntry *entries = ARRPTR(in); | |
+ bool is_number; | |
+ StringInfo src, | |
+ dst; | |
+ | |
+ if (count == 0) | |
+ { | |
+ out = palloc(1); | |
+ *out = '\0'; | |
+ PG_RETURN_TEXT_P(cstring_to_text(out)); | |
+ } | |
+ | |
+ buflen = 3; | |
+ | |
+ /* | |
+ * Formula adjusted slightly from the logic in hstore_out. We have to take | |
+ * account of out treatment of booleans to be a bit more pessimistic about | |
+ * the length of values. | |
+ */ | |
+ | |
+ for (i = 0; i < count; i++) | |
+ { | |
+ /* include "" and colon-space and comma-space */ | |
+ buflen += 6 + 2 * HS_KEYLEN(entries, i); | |
+ /* include "" only if nonnull */ | |
+ buflen += 3 + (HS_VALISNULL(entries, i) | |
+ ? 1 | |
+ : 2 * HS_VALLEN(entries, i)); | |
+ } | |
+ | |
+ out = ptr = palloc(buflen); | |
+ | |
+ src = makeStringInfo(); | |
+ dst = makeStringInfo(); | |
+ | |
+ *ptr++ = '{'; | |
+ | |
+ for (i = 0; i < count; i++) | |
+ { | |
+ resetStringInfo(src); | |
+ resetStringInfo(dst); | |
+ appendBinaryStringInfo(src, HS_KEY(entries, base, i), HS_KEYLEN(entries, i)); | |
+ escape_json(dst, src->data); | |
+ strncpy(ptr, dst->data, dst->len); | |
+ ptr += dst->len; | |
+ *ptr++ = ':'; | |
+ *ptr++ = ' '; | |
+ resetStringInfo(dst); | |
+ if (HS_VALISNULL(entries, i)) | |
+ appendStringInfoString(dst, "null"); | |
+ /* guess that values of 't' or 'f' are booleans */ | |
+ else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 't') | |
+ appendStringInfoString(dst, "true"); | |
+ else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 'f') | |
+ appendStringInfoString(dst, "false"); | |
+ else | |
+ { | |
+ is_number = false; | |
+ resetStringInfo(src); | |
+ appendBinaryStringInfo(src, 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 (src->len > 0 && | |
+ !(src->data[0] == '0' && isdigit((unsigned char) src->data[1])) && | |
+ strspn(src->data, "+-0123456789Ee.") == src->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(src->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(src->data, &endptr); | |
+ (void) dval; | |
+ if (*endptr == '\0') | |
+ is_number = true; | |
+ } | |
+ } | |
+ if (is_number) | |
+ appendBinaryStringInfo(dst, src->data, src->len); | |
+ else | |
+ escape_json(dst, src->data); | |
+ } | |
+ strncpy(ptr, dst->data, dst->len); | |
+ ptr += dst->len; | |
+ | |
+ if (i + 1 != count) | |
+ { | |
+ *ptr++ = ','; | |
+ *ptr++ = ' '; | |
+ } | |
+ } | |
+ *ptr++ = '}'; | |
+ *ptr = '\0'; | |
+ | |
+ PG_RETURN_TEXT_P(cstring_to_text(out)); | |
+} | |
+ | |
+PG_FUNCTION_INFO_V1(hstore_to_json); | |
+Datum hstore_to_json(PG_FUNCTION_ARGS); | |
+Datum | |
+hstore_to_json(PG_FUNCTION_ARGS) | |
+{ | |
+ HStore *in = PG_GETARG_HS(0); | |
+ int buflen, | |
+ i; | |
+ int count = HS_COUNT(in); | |
+ char *out, | |
+ *ptr; | |
+ char *base = STRPTR(in); | |
+ HEntry *entries = ARRPTR(in); | |
+ StringInfo src, | |
+ dst; | |
+ | |
+ if (count == 0) | |
+ { | |
+ out = palloc(1); | |
+ *out = '\0'; | |
+ PG_RETURN_TEXT_P(cstring_to_text(out)); | |
+ } | |
+ | |
+ buflen = 3; | |
+ | |
+ /* | |
+ * Formula adjusted slightly from the logic in hstore_out. We have to take | |
+ * account of out treatment of booleans to be a bit more pessimistic about | |
+ * the length of values. | |
+ */ | |
+ | |
+ for (i = 0; i < count; i++) | |
+ { | |
+ /* include "" and colon-space and comma-space */ | |
+ buflen += 6 + 2 * HS_KEYLEN(entries, i); | |
+ /* include "" only if nonnull */ | |
+ buflen += 3 + (HS_VALISNULL(entries, i) | |
+ ? 1 | |
+ : 2 * HS_VALLEN(entries, i)); | |
+ } | |
+ | |
+ out = ptr = palloc(buflen); | |
+ | |
+ src = makeStringInfo(); | |
+ dst = makeStringInfo(); | |
+ | |
+ *ptr++ = '{'; | |
+ | |
+ for (i = 0; i < count; i++) | |
+ { | |
+ resetStringInfo(src); | |
+ resetStringInfo(dst); | |
+ appendBinaryStringInfo(src, HS_KEY(entries, base, i), HS_KEYLEN(entries, i)); | |
+ escape_json(dst, src->data); | |
+ strncpy(ptr, dst->data, dst->len); | |
+ ptr += dst->len; | |
+ *ptr++ = ':'; | |
+ *ptr++ = ' '; | |
+ resetStringInfo(dst); | |
+ if (HS_VALISNULL(entries, i)) | |
+ appendStringInfoString(dst, "null"); | |
+ else | |
+ { | |
+ resetStringInfo(src); | |
+ appendBinaryStringInfo(src, HS_VAL(entries, base, i), HS_VALLEN(entries, i)); | |
+ escape_json(dst, src->data); | |
+ } | |
+ strncpy(ptr, dst->data, dst->len); | |
+ ptr += dst->len; | |
+ | |
+ if (i + 1 != count) | |
+ { | |
+ *ptr++ = ','; | |
+ *ptr++ = ' '; | |
+ } | |
+ } | |
+ *ptr++ = '}'; | |
+ *ptr = '\0'; | |
+ | |
+ PG_RETURN_TEXT_P(cstring_to_text(out)); | |
+} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Example :
testdb=# select hstore_to_json('"a"=>1,"b"=>2'::hstore);
hstore_to_json
{"a": "1", "b": "2"}
(1 row)
testdb=# select hstore_to_json_loose('"a"=>1,"b"=>2'::hstore);
hstore_to_json_loose
{"a": 1, "b": 2}
(1 row)