Re: [HACKERS] JSON output functions.
Hi Andrew Nice work! Just for completeness: Did you also think of including geometry types in JSON output functions in later releases? There's a nice extension of JSON called GeoJSON for a starting point. Yours, Stefan 2012/2/3 Andrew Dunstan and...@dunslane.net: On 02/02/2012 12:20 PM, Pavel Stehule wrote: 2012/2/2 Andrew Dunstanand...@dunslane.net: On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote: At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote: For now I'm inclined not to proceed with that, and leave it as an optimization to be considered later if necessary. Thoughts? I agree, there doesn't seem to be a pressing need to do it now. OK, here's my final version of the patch for constructor functions. If there's no further comment I'll go with this. These function are super, Thank you Do you plan to fix a issue with row attribute names in 9.2? Yeah. Tom did some initial work which he published here: http://archives.postgresql.org/message-id/28413.1321500388%40sss.pgh.pa.us, noting: It's not really ideal with respect to the ValuesScan case, because what you get seems to always be the hard-wired columnN names for VALUES columns, even if you try to override that with an alias ... Curiously, it works just fine if the VALUES can be folded and later he said: Upon further review, this patch would need some more work even for the RowExpr case, because there are several places that build RowExprs without bothering to build a valid colnames list. It's clearly soluble if anyone cares to put in the work, but I'm not personally excited enough to pursue it .. I'm going to look at that issue first, since the unfolded VALUES clause seems like something of an obscure corner case. Feel free to chime in if you can. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON output functions.
On 02/05/2012 02:31 PM, Stefan Keller wrote: Hi Andrew Nice work! Just for completeness: Did you also think of including geometry types in JSON output functions in later releases? There's a nice extension of JSON called GeoJSON for a starting point. [side note: please don't top-reply on -hackers. See http://idallen.com/topposting.html] Currently, in array_to_json and row_to_json the only special cases are: * record types are output as JSON records * array types are output as JSON arrays * numeric types are output without quoting * boolean types are output as unquoted true or false * NULLs are output as NULL Everything else is output as its text representation, suitably quoted and escaped. If you want to change how those operate, now rather than later would be the best time. But later we could certainly add various other foo_to_json functions for things like geometry types and hstores. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON output functions.
At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote: For now I'm inclined not to proceed with that, and leave it as an optimization to be considered later if necessary. Thoughts? I agree, there doesn't seem to be a pressing need to do it now. -- ams -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON output functions.
On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote:
At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote:
For now I'm inclined not to proceed with that, and leave it as an
optimization to be considered later if necessary. Thoughts?
I agree, there doesn't seem to be a pressing need to do it now.
OK, here's my final version of the patch for constructor functions. If
there's no further comment I'll go with this.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ec14004..22adcb8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9617,6 +9617,65 @@ table2-mapping
/sect2
/sect1
+ sect1 id=functions-json
+ titleJSON functions/title
+
+ indexterm zone=datatype-json
+ primaryJSON/primary
+ secondaryFunctions and operators/secondary
+ /indexterm
+
+ para
+This section descripbes the functions that are available for creating
+JSON (see xref linkend=datatype-json) data.
+ /para
+
+ table id=functions-json-table
+titleJSON Support Functions/title
+tgroup cols=4
+ thead
+ row
+ entryFunction/entry
+ entryDescription/entry
+ entryExample/entry
+ entryExample Result/entry
+ /row
+ /thead
+ tbody
+ row
+ entry
+ indexterm
+ primaryarray_to_json/primary
+ /indexterm
+ literalarray_to_json(anyarray [, pretty_bool])/literal
+ /entry
+ entry
+ Returns the array as JSON. A Postgres multi-dimensional array
+ becomes a JSON array of arrays. Line feeds will be added between
+ dimension 1 elements if pretty_bool is true.
+ /entry
+ entryliteralarray_to_json('{{1,5},{99,100}}'::int[])/literal/entry
+ entryliteral[[1,5],[99,100]]/literal/entry
+ /row
+ row
+ entry
+ indexterm
+ primaryrow_to_json/primary
+ /indexterm
+ literalrow_to_json(record [, pretty_bool])/literal
+ /entry
+ entry
+ Returns the row as JSON. Line feeds will be added between level
+ 1 elements if pretty_bool is true.
+ /entry
+ entryliteralrow_to_json(row(1,'foo'))/literal/entry
+ entryliteral{f1:1,f2:foo}/literal/entry
+ /row
+ /tbody
+/tgroup
+ /table
+
+ /sect1
sect1 id=functions-sequence
titleSequence Manipulation Functions/title
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e35ac59..e57580e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -24,6 +24,7 @@
#include rewrite/rewriteHandler.h
#include tcop/tcopprot.h
#include utils/builtins.h
+#include utils/json.h
#include utils/lsyscache.h
#include utils/rel.h
#include utils/snapmgr.h
@@ -99,7 +100,6 @@ static void ExplainDummyGroup(const char *objtype, const char *labelname,
static void ExplainXMLTag(const char *tagname, int flags, ExplainState *es);
static void ExplainJSONLineEnding(ExplainState *es);
static void ExplainYAMLLineStarting(ExplainState *es);
-static void escape_json(StringInfo buf, const char *str);
static void escape_yaml(StringInfo buf, const char *str);
@@ -2319,51 +2319,6 @@ ExplainYAMLLineStarting(ExplainState *es)
}
/*
- * Produce a JSON string literal, properly escaping characters in the text.
- */
-static void
-escape_json(StringInfo buf, const char *str)
-{
- const char *p;
-
- appendStringInfoCharMacro(buf, '\');
- for (p = str; *p; p++)
- {
- switch (*p)
- {
- case '\b':
-appendStringInfoString(buf, \\b);
-break;
- case '\f':
-appendStringInfoString(buf, \\f);
-break;
- case '\n':
-appendStringInfoString(buf, \\n);
-break;
- case '\r':
-appendStringInfoString(buf, \\r);
-break;
- case '\t':
-appendStringInfoString(buf, \\t);
-break;
- case '':
-appendStringInfoString(buf, \\\);
-break;
- case '\\':
-appendStringInfoString(buf, );
-break;
- default:
-if ((unsigned char) *p ' ')
- appendStringInfo(buf, \\u%04x, (int) *p);
-else
- appendStringInfoCharMacro(buf, *p);
-break;
- }
- }
- appendStringInfoCharMacro(buf, '\');
-}
-
-/*
* YAML is a superset of JSON; unfortuantely, the YAML quoting rules are
* ridiculously complicated -- as documented in sections 5.3 and 7.3.3 of
* http://yaml.org/spec/1.2/spec.html -- so we chose to just quote everything.
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index cbb81d1..60addf2 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -13,11 +13,17 @@
*/
#include postgres.h
+#include catalog/pg_type.h
+#include executor/spi.h
#include lib/stringinfo.h
#include libpq/pqformat.h
#include mb/pg_wchar.h
+#include parser/parse_coerce.h
+#include utils/array.h
#include utils/builtins.h
+#include utils/lsyscache.h
#include utils/json.h
+#include utils/typcache.h
typedef enum
{
@@ -72,8 +78,11 @@ static void json_lex_number(JsonLexContext *lex,
Re: [HACKERS] JSON output functions.
2012/2/2 Andrew Dunstan and...@dunslane.net: On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote: At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote: For now I'm inclined not to proceed with that, and leave it as an optimization to be considered later if necessary. Thoughts? I agree, there doesn't seem to be a pressing need to do it now. OK, here's my final version of the patch for constructor functions. If there's no further comment I'll go with this. These function are super, Thank you Do you plan to fix a issue with row attribute names in 9.2? Regards Pavel cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON output functions.
On 02/02/2012 12:20 PM, Pavel Stehule wrote: 2012/2/2 Andrew Dunstanand...@dunslane.net: On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote: At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote: For now I'm inclined not to proceed with that, and leave it as an optimization to be considered later if necessary. Thoughts? I agree, there doesn't seem to be a pressing need to do it now. OK, here's my final version of the patch for constructor functions. If there's no further comment I'll go with this. These function are super, Thank you Do you plan to fix a issue with row attribute names in 9.2? Yeah. Tom did some initial work which he published here: http://archives.postgresql.org/message-id/28413.1321500388%40sss.pgh.pa.us, noting: It's not really ideal with respect to the ValuesScan case, because what you get seems to always be the hard-wired columnN names for VALUES columns, even if you try to override that with an alias ... Curiously, it works just fine if the VALUES can be folded and later he said: Upon further review, this patch would need some more work even for the RowExpr case, because there are several places that build RowExprs without bothering to build a valid colnames list. It's clearly soluble if anyone cares to put in the work, but I'm not personally excited enough to pursue it .. I'm going to look at that issue first, since the unfolded VALUES clause seems like something of an obscure corner case. Feel free to chime in if you can. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] JSON output functions.
I've just been running some timings of my JSON-producing functions, in
particular array_to_json, and comparing them with the current
XML-producing functions. Here's a typical result:
andrew=# explain analyse select array_to_json(array_agg(q),true)
from (select * from pg_attribute) q;
QUERY PLAN
-
Aggregate (cost=70.77..70.78 rows=1 width=203) (actual
time=38.919..38.920 rows=1 loops=1)
- Seq Scan on pg_attribute (cost=0.00..65.01 rows=2301
width=203) (actual time=0.007..1.454 rows=2253 loops=1)
Total runtime: 39.300 ms
(3 rows)
Time: 62.753 ms
andrew=# explain analyse select table_to_xml('pg_attribute',
true,false,'');
QUERY PLAN
Result (cost=0.00..0.26 rows=1 width=0) (actual
time=519.170..526.737 rows=1 loops=1)
Total runtime: 526.780 ms
(2 rows)
As you can see, producing the JSON is a heck of a lot faster than
producing the equivalent XML. I had thought it might be necessary for
good performance to cache the type output info in the FunctionCallInfo
structure, rather than fetch it for each Datum we output, but that
doesn't seem to be so. For now I'm inclined not to proceed with that,
and leave it as an optimization to be considered later if necessary.
Thoughts?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
