Hi all
Whenever I try to work with the new json types I trip over the lack of a
function to escape text to json. The attached patch against master
provides one by exposing the existing datum_to_json function to SQL.
I've used the name value_to_json, but I'm not sure it's necessarily the
right choice of name.
Please consider this for the 9.2 branch as well as to HEAD, as IMO it's
very important for basic usability of the json functionality. It applies
to 9.2 fine and passes "make check". I know it's late in the game, but
it's also a very small change and it's very hard to build up JSON data
structures other than simple rows or arrays without at minimum a way of
escaping `text' to json strings.
This feels basic enough that I'm wondering if there's a reason it wasn't
included from the start, but I don't see any comments in json.c talking
about anything like this, nor did I find any -hackers discussion about
it. I suspect it's just an oversight.
As value_to_json directly wraps datum_to_json it actually accepts record
and array types too. I didn't see any reason to prevent that and force
the user to instead use row_to_json or array_to_json for those cases. If
you don't want to accept this, I can provide a wrapper for escape_json
that only accepts a text argument instead, but I think *some* way to
escape text to JSON is vital to have in 9.2.
A docs patch will follow shortly if you're happy that this patch is
reasonable.
--
Craig Ringer
>From e829c8500b0e507cb70c1a87784c9395269e27bc Mon Sep 17 00:00:00 2001
From: Craig Ringer <ring...@ringerc.id.au>
Date: Mon, 13 Aug 2012 10:29:40 +0800
Subject: [PATCH] Implement value_to_json, exposing the existing datum_to_json
for use from SQL.
A generic json quoting function needs to be available from SQL to permit
the building of any JSON structures other than those produced from arrays
or rowtypes.
---
src/backend/utils/adt/json.c | 28 ++++++++++++
src/include/catalog/pg_proc.h | 4 ++
src/include/utils/json.h | 1 +
src/test/regress/expected/json.out | 91 ++++++++++++++++++++++++++++++++++++++
src/test/regress/sql/json.sql | 39 ++++++++++++++++
5 files changed, 163 insertions(+)
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
new file mode 100644
index 0425ac6..11babd3
*** a/src/backend/utils/adt/json.c
--- b/src/backend/utils/adt/json.c
*************** row_to_json_pretty(PG_FUNCTION_ARGS)
*** 1121,1126 ****
--- 1121,1154 ----
}
/*
+ * SQL function value_to_json(value)
+ *
+ * Wraps datum_to_json for use from SQL, so any element may be
+ * converted to json.
+ */
+ extern Datum
+ value_to_json(PG_FUNCTION_ARGS)
+ {
+ Datum arg0 = PG_GETARG_DATUM(0);
+ Oid arg0type = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ StringInfo result;
+ TYPCATEGORY tcategory;
+ Oid typoutput;
+ bool typisvarlena;
+
+ if (arg0type == JSONOID)
+ tcategory = TYPCATEGORY_JSON;
+ else
+ tcategory = TypeCategory(arg0type);
+
+ getTypeOutputInfo(arg0type, &typoutput, &typisvarlena);
+
+ result = makeStringInfo();
+ datum_to_json(arg0, PG_ARGISNULL(0), result, tcategory, typoutput);
+ PG_RETURN_TEXT_P(cstring_to_text(result->data));
+ }
+
+ /*
* Produce a JSON string literal, properly escaping characters in the text.
*/
void
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index 665918f..31e7772
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3155 ( row_to_json
*** 4091,4096 ****
--- 4091,4100 ----
DESCR("map row to json");
DATA(insert OID = 3156 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 114 "2249 16" _null_ _null_ _null_ _null_ row_to_json_pretty _null_ _null_ _null_ ));
DESCR("map row to json with optional pretty printing");
+ DATA(insert OID = 3164 ( value_to_json PGNSP PGUID 12 1 0 0 0 f f f f f f s 1 0 114 "2283" _null_ _null_ _null_ _null_ value_to_json _null_ _null_ _null_ ));
+ DESCR("Convert a simple value to json, quoting and escaping if necessary");
+ DATA(insert OID = 3169 ( value_to_json PGNSP PGUID 12 1 0 0 0 f f f f f f s 1 0 114 "25" _null_ _null_ _null_ _null_ value_to_json _null_ _null_ _null_ ));
+ DESCR("Convert a simple value to json, quoting and escaping if necessary");
/* uuid */
DATA(insert OID = 2952 ( uuid_in PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2950 "2275" _null_ _null_ _null_ _null_ uuid_in _null_ _null_ _null_ ));
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
new file mode 100644
index 0f38147..432ad24
*** a/src/include/utils/json.h
--- b/src/include/utils/json.h
*************** extern Datum array_to_json(PG_FUNCTION_A
*** 25,30 ****
--- 25,31 ----
extern Datum array_to_json_pretty(PG_FUNCTION_ARGS);
extern Datum row_to_json(PG_FUNCTION_ARGS);
extern Datum row_to_json_pretty(PG_FUNCTION_ARGS);
+ extern Datum value_to_json(PG_FUNCTION_ARGS);
extern void escape_json(StringInfo buf, const char *str);
#endif /* JSON_H */
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
new file mode 100644
index 2dfe7bb..41e011d
*** a/src/test/regress/expected/json.out
--- b/src/test/regress/expected/json.out
*************** FROM (SELECT '{"a":1,"b": [2,3,4,"d","e"
*** 433,435 ****
--- 433,526 ----
{"jsonfield":{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}}
(1 row)
+ -- value_to_json(json)
+ SELECT value_to_json('{"a":1}'::json);
+ value_to_json
+ ---------------
+ {"a":1}
+ (1 row)
+
+ -- value_to_json(text)
+ SELECT value_to_json('{"a":1}'::text);
+ value_to_json
+ ---------------
+ "{\"a\":1}"
+ (1 row)
+
+ -- value_to_json(unspecified-text)
+ SELECT value_to_json('{"a":1}');
+ value_to_json
+ ---------------
+ "{\"a\":1}"
+ (1 row)
+
+ -- value_to_json(integer)
+ SELECT value_to_json(24);
+ value_to_json
+ ---------------
+ 24
+ (1 row)
+
+ -- value_to_json(float)
+ SELECT value_to_json('24.9'::float8);
+ value_to_json
+ ---------------
+ 24.9
+ (1 row)
+
+ -- value_to_json(NaN)
+ SELECT value_to_json('NaN'::float8);
+ value_to_json
+ ---------------
+ "NaN"
+ (1 row)
+
+ -- value_to_json(Infinity)
+ SELECT value_to_json('Infinity'::float8);
+ value_to_json
+ ---------------
+ "Infinity"
+ (1 row)
+
+ -- value_to_json(-Infinity)
+ SELECT value_to_json('-Infinity'::float8);
+ value_to_json
+ ---------------
+ "-Infinity"
+ (1 row)
+
+ -- value_to_json(json[])
+ SELECT value_to_json(ARRAY[ '1'::json, '"4"'::json, '{"a":1}'::json ]::json[]);
+ value_to_json
+ -----------------
+ [1,"4",{"a":1}]
+ (1 row)
+
+ -- value_to_json(text[])
+ SELECT value_to_json(ARRAY[ '1', '"4"', '{"a":1}' ]::text[]);
+ value_to_json
+ ---------------------------
+ ["1","\"4\"","{\"a\":1}"]
+ (1 row)
+
+ -- value_to_json(int[])
+ SELECT value_to_json(ARRAY[1,2,3,4,-1]);
+ value_to_json
+ ---------------
+ [1,2,3,4,-1]
+ (1 row)
+
+ -- value_to_json(row)
+ SELECT value_to_json(row(1,'foo'));
+ value_to_json
+ ---------------
+ "(1,foo)"
+ (1 row)
+
+ -- value_to_json(emptyrow)
+ SELECT value_to_json(row());
+ value_to_json
+ ---------------
+ "()"
+ (1 row)
+
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
new file mode 100644
index 52be0cf..8a0c3a5
*** a/src/test/regress/sql/json.sql
--- b/src/test/regress/sql/json.sql
*************** FROM (SELECT '-Infinity'::float8 AS "flo
*** 113,115 ****
--- 113,154 ----
-- json input
SELECT row_to_json(q)
FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q;
+
+ -- value_to_json(json)
+ SELECT value_to_json('{"a":1}'::json);
+
+ -- value_to_json(text)
+ SELECT value_to_json('{"a":1}'::text);
+
+ -- value_to_json(unspecified-text)
+ SELECT value_to_json('{"a":1}');
+
+ -- value_to_json(integer)
+ SELECT value_to_json(24);
+
+ -- value_to_json(float)
+ SELECT value_to_json('24.9'::float8);
+
+ -- value_to_json(NaN)
+ SELECT value_to_json('NaN'::float8);
+
+ -- value_to_json(Infinity)
+ SELECT value_to_json('Infinity'::float8);
+
+ -- value_to_json(-Infinity)
+ SELECT value_to_json('-Infinity'::float8);
+
+ -- value_to_json(json[])
+ SELECT value_to_json(ARRAY[ '1'::json, '"4"'::json, '{"a":1}'::json ]::json[]);
+
+ -- value_to_json(text[])
+ SELECT value_to_json(ARRAY[ '1', '"4"', '{"a":1}' ]::text[]);
+
+ -- value_to_json(int[])
+ SELECT value_to_json(ARRAY[1,2,3,4,-1]);
+
+ -- value_to_json(row)
+ SELECT value_to_json(row(1,'foo'));
+
+ -- value_to_json(emptyrow)
+ SELECT value_to_json(row());
--
1.7.11.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
