Re: [PATCH] Generalized JSON output functions

On Wed, Jul 15, 2015 at 8:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:

FWIW, I don't agree.  If it's not easy to read the JSON that
PostgreSQL generates using JavaScript, then a lot of people are just
going to give up on doing it, and IMO that would be sad.  Telling
people that they have to parse the JSON using some parser other than
the one built into their JavaScript engine, whack it around, and then
render it as text and parse it again is not really an acceptable
answer.

The vast majority of Javascript users are going to be using Node.js when they connect to Postgres if only for security reasons. If they use Node, they will be using node-postgres [1] or something that builds on top of it. For int64 and numerics in a row, the default is to return a string, and there is a flag you can set to round returned numbers if you prefer. There is also a way to override the default parsing of each Postgres type [2]. So in the case of JSON using my json-bignum module [3], the code looks like this:

    var pgTypes = require('pg').types;

    var bignumJSON = require('json-bignum');

    types.setTypeParser(JSON_TYPE_OID, function (value) {

        return bignumJSON.parse(value);

    });

    types.setTypeParser(JSONB_TYPE_OID, function (value) {

        return bignumJSON.parse(value);

    });

To me that code is super simple, and no a pain in the ass. In other words, it is not "Telling people that they have to parse the JSON using some parser other than the one built into their JavaScript engine, whack it around, and then render it as text and parse it again". Like I said previously, the situation with Javascript will hopefully be remedied in a few years with ES7 anyway.

1. https://github.com/brianc/node-postgres

2. https://github.com/brianc/node-pg-types

3. https://github.com/datalanche/json-bignum

 On Wed, Jul 15, 2015 at 8:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:

The reason why the logical decoding stuff allows multiple
output formats is because Andres, quite correctly, foresaw that
different people would need different output formats.  He could have
designed that system to output only one output format and just said,
everybody's got to read and parse this, but that would have been slow.
Instead, he tried to set things up so that you could get the output in
the format that was most convenient for your client, whatever that is.
On this thread, we're back-pedaling from that idea: sorry, you can get
JSON output, but if you want JSON output that will be properly
interpreted by your JSON parser, you can't have that.  Regardless of
the details of this particular patch, I can't endorse that approach.
If we want people to use our software, we need to meet them where they
are at, especially when we are only (IIUC) talking about inserting a
few extra quotation marks.

I would be okay with a generic way to specify output formats if there are many use cases beyond Javascript and JSON. I vaguely remember someone suggesting a FORMAT clause on CREATE TABLE which would specify how a particular column would output from a SELECT. For example, returning a date with a non-ISO format. I liked that idea. However if the only reason for different output formats is Javascript, that is silly. I have a very long list of feature requests that would probably only be beneficial to me or a handful of users. Should we implement them? No, of course not! If we did that Postgres would cease to be the best open-source database. You can't have the best product and say yes to everything. Feature creep is the enemy of quality. If Javascript is the sole reason for supporting multiple output formats, then that is the definition of feature creep in my opinion. If there are many use cases beyond Javascript and JSON, then that is different and a conversation worth having.