Skip to content

Instantly share code, notes, and snippets.

@luzluna

luzluna/hstore_to_json.patch

Created April 4, 2013 02:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save luzluna/5307140 to your computer and use it in GitHub Desktop.
Save luzluna/5307140 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres 9.2.3 hstore patch for hstore_to_json(), hstore_to_json_loose() function. backport from 9.3 development branch.
Raw
hstore_to_json.patch
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));
+}
@luzluna
Copy link
Author

luzluna commented Apr 4, 2013

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment