OK, opr_sanity was failing because I added the value_to_json(text) alias
to ensure that:
value_to_json('some_literal')
worked, following the same approach as quote_literal(anyelement) and
quote_literal(text). That should be reasonable, right? The comments on
the affected check in opr_sanity say that it's not necessarily wrong so
long as the called function is prepared to handle the different
arguments its self - which it is, since it's already accepting anyelement.
The test comment reads:
Note that the expected output of this part of the test will
need to be modified whenever new pairs of types are made
binary-equivalent,
or when new polymorphic built-in functions are added
so that seems reasonable.
postgres=# \df quote_literal
List of functions
Schema | Name | Result data type | Argument data types |
Type
------------+---------------+------------------+---------------------+--------
pg_catalog | quote_literal | text | anyelement | normal
pg_catalog | quote_literal | text | text | normal
(2 rows)
postgres=# \df value_to_json
List of functions
Schema | Name | Result data type | Argument data types |
Type
------------+---------------+------------------+---------------------+--------
pg_catalog | value_to_json | json | anyelement | normal
pg_catalog | value_to_json | json | text | normal
(2 rows)
Revised patch that tweaks the expected result of opr_sanity attached.
--
Craig Ringer
[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/expected/opr_sanity.out | 3 +-
src/test/regress/sql/json.sql | 39 ++++++++++++++
6 files changed, 165 insertions(+), 1 deletion(-)
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/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
new file mode 100644
index 110ea41..a7a3576
*** a/src/test/regress/expected/opr_sanity.out
--- b/src/test/regress/expected/opr_sanity.out
*************** ORDER BY 1, 2;
*** 171,180 ****
-------------+-------------
25 | 1042
25 | 1043
1114 | 1184
1560 | 1562
2277 | 2283
! (5 rows)
SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
FROM pg_proc AS p1, pg_proc AS p2
--- 171,181 ----
-------------+-------------
25 | 1042
25 | 1043
+ 25 | 2283
1114 | 1184
1560 | 1562
2277 | 2283
! (6 rows)
SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
FROM pg_proc AS p1, pg_proc AS p2
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
