On 02/24/2013 01:09 AM, Steve Singer wrote:
On 13-01-11 11:03 AM, Andrew Dunstan wrote:
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.
Here is a review of this patch.,
Overview
---------------------
This patch adds a set of functions to convert types to json. Specifically
* An aggregate that take the elements and builds up a json array.
* Conversions from an hstore to json
For non-builtin types the text conversion is used unless a cast has
specifically been defined from the type to json.
There was some discussion last year on this patch that raised three
issues
a) Robert was concerned that if someone added a cast from a non-core
type like citext to json that it would change the behaviour of this
function. No one else offered an opinion on this at the time. I don't
see this as a problem, if I add a cast between citext (or any other
non-core datatype) to json I would expect it to effect how that
datatype is generated as a json object in any functions that generate
json. I don't see why this would be surprising behaviour. If I add
a cast between my datatype and json to generate a json representation
that differs from the textout representation then I would expect that
representation to be used in the json_agg function as well.
b) There was some talk in the json bikeshedding thread that json_agg()
should be renamed to collect_json() but more people preferred json_agg().
c) Should an aggregate of an empty result produce NULL or an empty
json element. Most people who commented on the thread seemed to feel
that NULL is preferred because it is consistent with other aggregates
I feel that the functionality of this patch is fine.
Testing
-------------------
When I try running the regression tests with this patch I get:
creating template1 database in
/usr/local/src/postgresql/src/test/regress/./tmp_check/data/base/1 ...
FATAL: could not create unique index "pg_proc_oid_index"
DETAIL: Key (oid)=(3171) is duplicated.
child process exited with exit code 1
oid 3170, 3171 and 3172 appears to be used by lo_tell64 and lo_lseek64
If I fix those up the regression tests pass.
I tried using the new functions in a few different ways and everything
worked as expected.
Code Review
-----------
in contrib/hstore/hstore_io.c
+ /* 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;
I dislike that we have to do this to avoid compiler warnings. I am
also worried the some other compiler might decide that the else if
(false) is worthy of a warning. Does anyone have cleaner way of
getting rid of the warning we get if we don't store the strtol/strtod
result?
Should we do something like:
(void) ( strtol(src->data,&endptr,10)+1);
Other than that I don't see any issues.
Thanks for the review. Revised patch attached with fresh OIDs and
numeric detection code cleaned up along the lines you suggest.
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,1428 ----
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 = "junk";
+
+ (void) (strtol(src->data, &endptr, 10) + 1);
+ if (*endptr == '\0')
+ {
+ /*
+ * strol man page says this means the whole string is
+ * valid
+ */
+ is_number = true;
+ }
+ else
+ {
+ /* not an int - try a double */
+ (void) (strtod(src->data, &endptr) + 1.0);
+ 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));
+ }
*** 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
***************
*** 9685,9694 **** table2-mapping
--- 9685,9725 ----
<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">
***************
*** 11062,11067 **** SELECT NULLIF(value, '(none)') ...
--- 11093,11114 ----
<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>
***************
*** 11204,11209 **** SELECT count(*) FROM sometable;
--- 11251,11257 ----
<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 */
***************
*** 42,48 **** typedef struct /* state of JSON lexer */
{
char *input; /* whole string being parsed */
char *token_start; /* start of current token within input */
! char *token_terminator; /* end of previous or current token */
JsonValueType token_type; /* type of current token, once it's known */
} JsonLexContext;
--- 45,51 ----
{
char *input; /* whole string being parsed */
char *token_start; /* start of current token within input */
! char *token_terminator; /* end of previous or current token */
JsonValueType token_type; /* type of current token, once it's known */
} JsonLexContext;
***************
*** 67,73 **** typedef enum /* required operations on state stack */
{
JSON_STACKOP_NONE, /* no-op */
JSON_STACKOP_PUSH, /* push new JSON_PARSE_VALUE stack item */
! JSON_STACKOP_PUSH_WITH_PUSHBACK, /* push, then rescan current token */
JSON_STACKOP_POP /* pop, or expect end of input if no stack */
} JsonStackOp;
--- 70,76 ----
{
JSON_STACKOP_NONE, /* no-op */
JSON_STACKOP_PUSH, /* push new JSON_PARSE_VALUE stack item */
! JSON_STACKOP_PUSH_WITH_PUSHBACK, /* push, then rescan current token */
JSON_STACKOP_POP /* pop, or expect end of input if no stack */
} JsonStackOp;
***************
*** 77,95 **** static void json_lex_string(JsonLexContext *lex);
static void json_lex_number(JsonLexContext *lex, char *s);
static void report_parse_error(JsonParseStack *stack, JsonLexContext *lex);
static void report_invalid_token(JsonLexContext *lex);
! static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
! bool use_line_feeds);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
TYPCATEGORY tcategory, Oid typoutputfunc,
bool use_line_feeds);
static void array_to_json_internal(Datum array, StringInfo result,
! bool use_line_feeds);
/* fake type category for JSON so we can distinguish it in datum_to_json */
#define TYPCATEGORY_JSON 'j'
/* 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 */
--- 80,104 ----
static void json_lex_number(JsonLexContext *lex, char *s);
static void report_parse_error(JsonParseStack *stack, JsonLexContext *lex);
static void report_invalid_token(JsonLexContext *lex);
! static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
! bool use_line_feeds);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
TYPCATEGORY tcategory, Oid typoutputfunc,
bool use_line_feeds);
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 */
***************
*** 384,398 **** json_lex(JsonLexContext *lex)
* unintuitive prefix thereof.
*/
for (p = s; JSON_ALPHANUMERIC_CHAR(*p); p++)
! /* skip */ ;
if (p == s)
{
/*
* We got some sort of unexpected punctuation or an otherwise
* unexpected character, so just complain about that one
! * character. (It can't be multibyte because the above loop
! * will advance over any multibyte characters.)
*/
lex->token_terminator = s + 1;
report_invalid_token(lex);
--- 393,407 ----
* unintuitive prefix thereof.
*/
for (p = s; JSON_ALPHANUMERIC_CHAR(*p); p++)
! /* skip */ ;
if (p == s)
{
/*
* We got some sort of unexpected punctuation or an otherwise
* unexpected character, so just complain about that one
! * character. (It can't be multibyte because the above loop will
! * advance over any multibyte characters.)
*/
lex->token_terminator = s + 1;
report_invalid_token(lex);
***************
*** 585,591 **** json_lex_number(JsonLexContext *lex, char *s)
}
/*
! * Check for trailing garbage. As in json_lex(), any alphanumeric stuff
* here should be considered part of the token for error-reporting
* purposes.
*/
--- 594,600 ----
}
/*
! * Check for trailing garbage. As in json_lex(), any alphanumeric stuff
* here should be considered part of the token for error-reporting
* purposes.
*/
***************
*** 653,668 **** report_parse_error(JsonParseStack *stack, JsonLexContext *lex)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected \",\" or \"]\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_START:
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected string or \"}\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_LABEL:
--- 662,677 ----
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected \",\" or \"]\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_START:
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected string or \"}\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_LABEL:
***************
*** 677,684 **** report_parse_error(JsonParseStack *stack, JsonLexContext *lex)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected \",\" or \"}\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_COMMA:
--- 686,693 ----
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type json"),
! errdetail("Expected \",\" or \"}\", but found \"%s\".",
! token),
report_json_context(lex)));
break;
case JSON_PARSE_OBJECT_COMMA:
***************
*** 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,1404 ----
}
/*
+ * 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
***************
*** 4104,4109 **** DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 1
--- 4104,4117 ----
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 = 3173 ( 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 = 3174 ( 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 = 3175 ( 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 = 3176 ( 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
