On 01/11/2013 11:00 AM, Andrew Dunstan wrote:
I have not had anyone follow up on this, so I have added docs and will
add this to the commitfest.
Recap:
This adds the following:
json_agg(anyrecord) -> json
to_json(any) -> json
hstore_to_json(hstore) -> json (also used as a cast)
hstore_to_json_loose(hstore) -> json
Also, in json generation, if any non-builtin type has a cast to json,
that function is used instead of the type's output function.
This time with a patch.
cheers
andrew
*** a/contrib/hstore/expected/hstore.out
--- b/contrib/hstore/expected/hstore.out
***************
*** 1453,1455 **** select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe
--- 1453,1491 ----
1
(1 row)
+ -- json
+ 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
+ -------------------------------------------------------------------------------------------------
+ {"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 json);
+ json
+ -------------------------------------------------------------------------------------------------
+ {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
+ (1 row)
+
+ select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
+ hstore_to_json_loose
+ ------------------------------------------------------------------------------------------
+ {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "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');
+ select json_agg(q) from test_json_agg q;
+ json_agg
+ ----------------------------------------------------------------------------------------------------------------------------
+ [{"f1":"rec1","f2":{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}}, +
+ {"f1":"rec2","f2":{"b": "f", "c": "null", "d": "-12345", "e": "012345.6", "f": "-1.234", "g": "0.345e-4", "a key": "2"}}]
+ (1 row)
+
+ select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
+ json_agg
+ ----------------------------------------------------------------------------------------------------------------------
+ [{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}}, +
+ {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}]
+ (1 row)
+
*** a/contrib/hstore/hstore--1.1.sql
--- b/contrib/hstore/hstore--1.1.sql
***************
*** 234,239 **** LANGUAGE C IMMUTABLE STRICT;
--- 234,252 ----
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'
*** a/contrib/hstore/hstore_io.c
--- b/contrib/hstore/hstore_io.c
***************
*** 8,14 ****
--- 8,17 ----
#include "access/htup_details.h"
#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"
***************
*** 1209,1211 **** hstore_send(PG_FUNCTION_ARGS)
--- 1212,1425 ----
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(src->data[1])) &&
+ strspn(src->data,"+-0123456789Ee.") == src->len)
+ {
+ /* might be a number. See if we can input it as a numeric value*/
+ char * endptr;
+ long longres = strtol(src->data,&endptr,10);
+ 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 doubleres = strtod(src->data,&endptr);
+ if (*endptr == '\0')
+ is_number = true;
+ else if (false)
+ {
+ /* shut the compiler up about unused variables */
+ longres = (long) doubleres;
+ longres = longres / 2;
+ }
+ }
+ }
+ 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));
+ }
+
*** a/contrib/hstore/sql/hstore.sql
--- b/contrib/hstore/sql/hstore.sql
***************
*** 330,332 **** set enable_seqscan=off;
--- 330,344 ----
select count(*) from testhstore where h #># 'p=>1';
select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
+
+ -- json
+ 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');
+
+ 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');
+ select json_agg(q) from test_json_agg q;
+ select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
+
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 9671,9680 **** table2-mapping
--- 9671,9711 ----
<entry><literal>row_to_json(row(1,'foo'))</literal></entry>
<entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>to_json</primary>
+ </indexterm>
+ <literal>to_json(anyelement)</literal>
+ </entry>
+ <entry>
+ Returns the value as JSON. If the data type is not builtin, and there
+ is a cast from the type to json, the cast function will be used to
+ perform the conversion. Otherwise, for any value other than a number,
+ a boolean or NULL, the text representation will be used, escaped and
+ quoted so that it is legal JSON.
+ </entry>
+ <entry><literal>to_json('Fred said "Hi."'</literal></entry>
+ <entry><literal>"Fred said \"Hi.\""</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
+ <note>
+ <para>
+ The <xref linkend="hstore"> extension has a cast from hstore to
+ json, so that converted hstore values are represented as json objects,
+ not as string values.
+ </para>
+ </note>
+
+ <para>
+ See also <xref linkend="functions-aggregate"> about the aggregate
+ function <function>json_agg</function> which aggregates record
+ values as json efficiently.
+ </para>
+
</sect1>
<sect1 id="functions-sequence">
***************
*** 11048,11053 **** SELECT NULLIF(value, '(none)') ...
--- 11079,11100 ----
<row>
<entry>
<indexterm>
+ <primary>json_agg</primary>
+ </indexterm>
+ <function>json_agg(<replaceable class="parameter">record</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>record</type>
+ </entry>
+ <entry>
+ <type>json</type>
+ </entry>
+ <entry>aggregates records as a json array of objects</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>max</primary>
</indexterm>
<function>max(<replaceable class="parameter">expression</replaceable>)</function>
***************
*** 11190,11195 **** SELECT count(*) FROM sometable;
--- 11237,11243 ----
<para>
The aggregate functions <function>array_agg</function>,
+ <function>json_agg</function>,
<function>string_agg</function>,
and <function>xmlagg</function>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
*** a/doc/src/sgml/hstore.sgml
--- b/doc/src/sgml/hstore.sgml
***************
*** 323,328 **** b
--- 323,344 ----
</row>
<row>
+ <entry><function>hstore_to_json(hstore)</function></entry>
+ <entry><type>json</type></entry>
+ <entry>get <type>hstore</type> as a json value</entry>
+ <entry><literal>hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</literal></entry>
+ <entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
+ </row>
+
+ <row>
+ <entry><function>hstore_to_json_loose(hstore)</function></entry>
+ <entry><type>json</type></entry>
+ <entry>get <type>hstore</type> as a json value, but attempting to distinguish numerical and boolean values so they are unquoted in the json</entry>
+ <entry><literal>hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</literal></entry>
+ <entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
+ </row>
+
+ <row>
<entry><function>slice(hstore, text[])</function></entry>
<entry><type>hstore</type></entry>
<entry>extract a subset of an <type>hstore</></entry>
***************
*** 398,403 **** b
--- 414,426 ----
<note>
<para>
+ The function <function>hstore_to_json</function> is used when an <type>hstore</type>
+ value is cast to <type>json</type>.
+ </para>
+ </note>
+
+ <note>
+ <para>
The function <function>populate_record</function> is actually declared
with <type>anyelement</>, not <type>record</>, as its first argument,
but it will reject non-record types with a run-time error.
*** a/src/backend/utils/adt/json.c
--- b/src/backend/utils/adt/json.c
***************
*** 14,19 ****
--- 14,21 ----
#include "postgres.h"
#include "access/htup_details.h"
+ #include "access/transam.h"
+ #include "catalog/pg_cast.h"
#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "lib/stringinfo.h"
***************
*** 24,29 ****
--- 26,32 ----
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/json.h"
+ #include "utils/syscache.h"
#include "utils/typcache.h"
typedef enum /* types of JSON values */
***************
*** 88,95 **** static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
--- 91,104 ----
static void array_to_json_internal(Datum array, StringInfo result,
bool use_line_feeds);
+ /*
+ * All the defined type categories are upper case , so use lower case here
+ * so we avoid any possible clash.
+ */
/* fake type category for JSON so we can distinguish it in datum_to_json */
#define TYPCATEGORY_JSON 'j'
+ /* fake category for types that have a cast to json */
+ #define TYPCATEGORY_JSON_CAST 'c'
/* letters appearing in numeric output that aren't valid in a JSON number */
#define NON_NUMERIC_LETTER "NnAaIiFfTtYy"
/* chars to consider as part of an alphanumeric token */
***************
*** 820,825 **** datum_to_json(Datum val, bool is_null, StringInfo result,
--- 829,835 ----
TYPCATEGORY tcategory, Oid typoutputfunc)
{
char *outputstr;
+ text *jsontext;
if (is_null)
{
***************
*** 862,867 **** datum_to_json(Datum val, bool is_null, StringInfo result,
--- 872,884 ----
appendStringInfoString(result, outputstr);
pfree(outputstr);
break;
+ case TYPCATEGORY_JSON_CAST:
+ jsontext = DatumGetTextP(OidFunctionCall1(typoutputfunc, val));
+ outputstr = text_to_cstring(jsontext);
+ appendStringInfoString(result, outputstr);
+ pfree(outputstr);
+ pfree(jsontext);
+ break;
default:
outputstr = OidOutputFunctionCall(typoutputfunc, val);
escape_json(result, outputstr);
***************
*** 935,940 **** array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
--- 952,958 ----
Oid typioparam;
Oid typoutputfunc;
TYPCATEGORY tcategory;
+ Oid castfunc = InvalidOid;
ndim = ARR_NDIM(v);
dim = ARR_DIMS(v);
***************
*** 950,960 **** array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
&typlen, &typbyval, &typalign,
&typdelim, &typioparam, &typoutputfunc);
deconstruct_array(v, element_type, typlen, typbyval,
typalign, &elements, &nulls,
&nitems);
! if (element_type == RECORDOID)
tcategory = TYPCATEGORY_COMPOSITE;
else if (element_type == JSONOID)
tcategory = TYPCATEGORY_JSON;
--- 968,999 ----
&typlen, &typbyval, &typalign,
&typdelim, &typioparam, &typoutputfunc);
+ if (element_type > FirstNormalObjectId)
+ {
+ HeapTuple tuple;
+ Form_pg_cast castForm;
+
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(element_type),
+ ObjectIdGetDatum(JSONOID));
+ if (HeapTupleIsValid(tuple))
+ {
+ castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+ if (castForm->castmethod == COERCION_METHOD_FUNCTION)
+ castfunc = typoutputfunc = castForm->castfunc;
+
+ ReleaseSysCache(tuple);
+ }
+ }
+
deconstruct_array(v, element_type, typlen, typbyval,
typalign, &elements, &nulls,
&nitems);
! if (castfunc != InvalidOid)
! tcategory = TYPCATEGORY_JSON_CAST;
! else if (element_type == RECORDOID)
tcategory = TYPCATEGORY_COMPOSITE;
else if (element_type == JSONOID)
tcategory = TYPCATEGORY_JSON;
***************
*** 1009,1014 **** composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
--- 1048,1054 ----
TYPCATEGORY tcategory;
Oid typoutput;
bool typisvarlena;
+ Oid castfunc = InvalidOid;
if (tupdesc->attrs[i]->attisdropped)
continue;
***************
*** 1023,1029 **** composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
origval = heap_getattr(tuple, i + 1, tupdesc, &isnull);
! if (tupdesc->attrs[i]->atttypid == RECORDARRAYOID)
tcategory = TYPCATEGORY_ARRAY;
else if (tupdesc->attrs[i]->atttypid == RECORDOID)
tcategory = TYPCATEGORY_COMPOSITE;
--- 1063,1093 ----
origval = heap_getattr(tuple, i + 1, tupdesc, &isnull);
! getTypeOutputInfo(tupdesc->attrs[i]->atttypid,
! &typoutput, &typisvarlena);
!
! if (tupdesc->attrs[i]->atttypid > FirstNormalObjectId)
! {
! HeapTuple cast_tuple;
! Form_pg_cast castForm;
!
! cast_tuple = SearchSysCache2(CASTSOURCETARGET,
! ObjectIdGetDatum(tupdesc->attrs[i]->atttypid),
! ObjectIdGetDatum(JSONOID));
! if (HeapTupleIsValid(cast_tuple))
! {
! castForm = (Form_pg_cast) GETSTRUCT(cast_tuple);
!
! if (castForm->castmethod == COERCION_METHOD_FUNCTION)
! castfunc = typoutput = castForm->castfunc;
!
! ReleaseSysCache(cast_tuple);
! }
! }
!
! if (castfunc != InvalidOid)
! tcategory = TYPCATEGORY_JSON_CAST;
! else if (tupdesc->attrs[i]->atttypid == RECORDARRAYOID)
tcategory = TYPCATEGORY_ARRAY;
else if (tupdesc->attrs[i]->atttypid == RECORDOID)
tcategory = TYPCATEGORY_COMPOSITE;
***************
*** 1032,1040 **** composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
else
tcategory = TypeCategory(tupdesc->attrs[i]->atttypid);
- getTypeOutputInfo(tupdesc->attrs[i]->atttypid,
- &typoutput, &typisvarlena);
-
/*
* If we have a toasted datum, forcibly detoast it here to avoid
* memory leakage inside the type's output routine.
--- 1096,1101 ----
***************
*** 1122,1127 **** row_to_json_pretty(PG_FUNCTION_ARGS)
--- 1183,1401 ----
}
/*
+ * SQL function to_json(anyvalue)
+ */
+ Datum
+ to_json(PG_FUNCTION_ARGS)
+ {
+ Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ StringInfo result;
+ Datum orig_val, val;
+ TYPCATEGORY tcategory;
+ Oid typoutput;
+ bool typisvarlena;
+ Oid castfunc = InvalidOid;
+
+ if (val_type == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+
+ result = makeStringInfo();
+
+ orig_val = PG_ARGISNULL(0) ? (Datum) 0 : PG_GETARG_DATUM(0);
+
+ getTypeOutputInfo(val_type, &typoutput, &typisvarlena);
+
+ if (val_type > FirstNormalObjectId)
+ {
+ HeapTuple tuple;
+ Form_pg_cast castForm;
+
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(val_type),
+ ObjectIdGetDatum(JSONOID));
+ if (HeapTupleIsValid(tuple))
+ {
+ castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+ if (castForm->castmethod == COERCION_METHOD_FUNCTION)
+ castfunc = typoutput = castForm->castfunc;
+
+ ReleaseSysCache(tuple);
+ }
+ }
+
+ if (castfunc != InvalidOid)
+ tcategory = TYPCATEGORY_JSON_CAST;
+ else if (val_type == RECORDARRAYOID)
+ tcategory = TYPCATEGORY_ARRAY;
+ else if (val_type == RECORDOID)
+ tcategory = TYPCATEGORY_COMPOSITE;
+ else if (val_type == JSONOID)
+ tcategory = TYPCATEGORY_JSON;
+ else
+ tcategory = TypeCategory(val_type);
+
+ /*
+ * If we have a toasted datum, forcibly detoast it here to avoid
+ * memory leakage inside the type's output routine.
+ */
+ if (typisvarlena && orig_val != (Datum) 0)
+ val = PointerGetDatum(PG_DETOAST_DATUM(orig_val));
+ else
+ val = orig_val;
+
+ datum_to_json(val, false, result, tcategory, typoutput);
+
+ /* Clean up detoasted copy, if any */
+ if (val != orig_val)
+ pfree(DatumGetPointer(val));
+
+ PG_RETURN_TEXT_P(cstring_to_text(result->data));
+ }
+
+ /*
+ * json_agg transition function
+ */
+ Datum
+ json_agg_transfn(PG_FUNCTION_ARGS)
+ {
+ Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ MemoryContext aggcontext, oldcontext;
+ StringInfo state;
+ Datum orig_val, val;
+ TYPCATEGORY tcategory;
+ Oid typoutput;
+ bool typisvarlena;
+ Oid castfunc = InvalidOid;
+
+ if (val_type == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "json_agg_transfn called in non-aggregate context");
+ }
+
+ if (PG_ARGISNULL(0))
+ {
+ /*
+ * Make this StringInfo in a context where it will persist
+ * for the duration off the aggregate call. It's only needed
+ * for this initial piece, as the StringInfo routines make sure
+ * they use the right context to enlarge the object if necessary.
+ */
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = makeStringInfo();
+ MemoryContextSwitchTo(oldcontext);
+
+ appendStringInfoChar(state,'[');
+ }
+ else
+ {
+ state = (StringInfo) PG_GETARG_POINTER(0);
+ appendStringInfoString(state, ", ");
+ }
+
+ /* fast path for NULLs */
+ if (PG_ARGISNULL(1))
+ {
+ orig_val = (Datum) 0;
+ datum_to_json(orig_val, true, state, 0, InvalidOid);
+ PG_RETURN_POINTER(state);
+ }
+
+
+ orig_val = PG_GETARG_DATUM(1);
+
+ getTypeOutputInfo(val_type, &typoutput, &typisvarlena);
+
+ if (val_type > FirstNormalObjectId)
+ {
+ HeapTuple tuple;
+ Form_pg_cast castForm;
+
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(val_type),
+ ObjectIdGetDatum(JSONOID));
+ if (HeapTupleIsValid(tuple))
+ {
+ castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+ if (castForm->castmethod == COERCION_METHOD_FUNCTION)
+ castfunc = typoutput = castForm->castfunc;
+
+ ReleaseSysCache(tuple);
+ }
+ }
+
+ if (castfunc != InvalidOid)
+ tcategory = TYPCATEGORY_JSON_CAST;
+ else if (val_type == RECORDARRAYOID)
+ tcategory = TYPCATEGORY_ARRAY;
+ else if (val_type == RECORDOID)
+ tcategory = TYPCATEGORY_COMPOSITE;
+ else if (val_type == JSONOID)
+ tcategory = TYPCATEGORY_JSON;
+ else
+ tcategory = TypeCategory(val_type);
+
+ /*
+ * If we have a toasted datum, forcibly detoast it here to avoid
+ * memory leakage inside the type's output routine.
+ */
+ if (typisvarlena)
+ val = PointerGetDatum(PG_DETOAST_DATUM(orig_val));
+ else
+ val = orig_val;
+
+ if (! PG_ARGISNULL(0) &&
+ (tcategory == TYPCATEGORY_ARRAY || tcategory == TYPCATEGORY_COMPOSITE))
+ {
+ appendStringInfoString(state,"\n ");
+ }
+
+ datum_to_json(val, false, state, tcategory, typoutput);
+
+ /* Clean up detoasted copy, if any */
+ if (val != orig_val)
+ pfree(DatumGetPointer(val));
+
+ /*
+ * The transition type for array_agg() is declared to be "internal", which
+ * is a pass-by-value type the same size as a pointer. So we can safely
+ * pass the ArrayBuildState pointer through nodeAgg.c's machinations.
+ */
+ PG_RETURN_POINTER(state);
+ }
+
+ /*
+ * json_agg final function
+ */
+ Datum
+ json_agg_finalfn(PG_FUNCTION_ARGS)
+ {
+ StringInfo state;
+
+ /* cannot be called directly because of internal-type argument */
+ Assert(AggCheckCallContext(fcinfo, NULL));
+
+ state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0);
+
+ if (state == NULL)
+ PG_RETURN_NULL();
+
+ appendStringInfoChar(state,']');
+
+ PG_RETURN_TEXT_P(cstring_to_text(state->data));
+ }
+
+ /*
* Produce a JSON string literal, properly escaping characters in the text.
*/
void
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
***************
*** 232,237 **** DATA(insert ( 3538 string_agg_transfn string_agg_finalfn 0 2281 _null_ ));
--- 232,240 ----
/* bytea */
DATA(insert ( 3545 bytea_string_agg_transfn bytea_string_agg_finalfn 0 2281 _null_ ));
+ /* json */
+ DATA(insert ( 3172 json_agg_transfn json_agg_finalfn 0 2281 _null_ ));
+
/*
* prototypes for functions in pg_aggregate.c
*/
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4102,4107 **** DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 1
--- 4102,4115 ----
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 = 3170 ( json_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2283" _null_ _null_ _null_ _null_ json_agg_transfn _null_ _null_ _null_ ));
+ DESCR("json aggregate transition function");
+ DATA(insert OID = 3171 ( json_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 114 "2281" _null_ _null_ _null_ _null_ json_agg_finalfn _null_ _null_ _null_ ));
+ DESCR("json aggregate final function");
+ DATA(insert OID = 3172 ( json_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 114 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("aggregate input into json");
+ DATA(insert OID = 3173 ( to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 114 "2283" _null_ _null_ _null_ _null_ to_json _null_ _null_ _null_ ));
+ DESCR("map input to json");
/* 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_ ));
*** a/src/include/utils/json.h
--- b/src/include/utils/json.h
***************
*** 25,30 **** extern Datum array_to_json(PG_FUNCTION_ARGS);
--- 25,35 ----
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 to_json(PG_FUNCTION_ARGS);
+
+ extern Datum json_agg_transfn(PG_FUNCTION_ARGS);
+ extern Datum json_agg_finalfn(PG_FUNCTION_ARGS);
+
extern void escape_json(StringInfo buf, const char *str);
#endif /* JSON_H */
*** a/src/test/regress/expected/json.out
--- b/src/test/regress/expected/json.out
***************
*** 403,408 **** SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
--- 403,432 ----
{"f1":[5,6,7,8,9,10]}
(1 row)
+ --json_agg
+ SELECT json_agg(q)
+ FROM ( SELECT $$a$$ || x AS b, y AS c,
+ ARRAY[ROW(x.*,ARRAY[1,2,3]),
+ ROW(y.*,ARRAY[4,5,6])] AS z
+ FROM generate_series(1,2) x,
+ generate_series(4,5) y) q;
+ json_agg
+ -----------------------------------------------------------------------
+ [{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
+ {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}, +
+ {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
+ {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}]
+ (1 row)
+
+ SELECT json_agg(q)
+ FROM rows q;
+ json_agg
+ -----------------------
+ [{"x":1,"y":"txt1"}, +
+ {"x":2,"y":"txt2"}, +
+ {"x":3,"y":"txt3"}]
+ (1 row)
+
-- non-numeric output
SELECT row_to_json(q)
FROM (SELECT 'NaN'::float8 AS "float8field") q;
*** a/src/test/regress/sql/json.sql
--- b/src/test/regress/sql/json.sql
***************
*** 100,105 **** FROM rows q;
--- 100,117 ----
SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
+ --json_agg
+
+ SELECT json_agg(q)
+ FROM ( SELECT $$a$$ || x AS b, y AS c,
+ ARRAY[ROW(x.*,ARRAY[1,2,3]),
+ ROW(y.*,ARRAY[4,5,6])] AS z
+ FROM generate_series(1,2) x,
+ generate_series(4,5) y) q;
+
+ SELECT json_agg(q)
+ FROM rows q;
+
-- non-numeric output
SELECT row_to_json(q)
FROM (SELECT 'NaN'::float8 AS "float8field") q;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
