Re: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ? - Mailing list pgsql-hackers

On Fri, 2012-05-04 at 15:59 -0400, Robert Haas wrote:
> On Fri, May 4, 2012 at 3:49 PM, Hannu Krosing <hannu@krosing.net> wrote:
> > On Fri, 2012-05-04 at 09:52 -0400, Tom Lane wrote:
> >> Hannu Krosing <hannu@2ndQuadrant.com> writes:
> >> > On Wed, 2012-05-02 at 12:06 -0700, Andrew Dunstan wrote:
> >> >> So given that do we do anything about this now, or wait till 9.3?
> >>
> >> > I'd like the json support in 9.2 updated as follows
> >>
> >> I think it's too late to be entertaining proposals for such changes in
> >> 9.2.  If we had concluded that the existing functions were actively
> >> wrong or a bad idea,
> >
> > I think that hard-coding "postgresql text" representation as our json
> > representation without a possibility for the user tio easily fix it
> > without rewriting foll xx_to_json() functions is borderline "actively
> > wrong".
> >
> > Can we at least have the xxx_to_json() functions try cast to json first
> > and fall back to text if the cast fails.
> 
> I think the idea that you can involve the casting machinery in this is
> misguided.  sometextval::json has got to mean that sometextval is
> expected to be in the form of a syntactically correct JSON value - and
> NOT that we wrap it in a JSON string.  

PostgreSQL CAST is different from casting a C pointer, they actually
perform a conversion when defined WITH FUNCTION or WITH INOUT. 
And they pass value unchanged when defined WITHOUT FUNCTION.

Casts _do_not_ perform syntax checks, they assume the source to be of
the type castted from and they return result of the target type.

I think you are confusing input/output formatting with CAST here.

This confusion about cast-as-syntax-check probably qualifies as
something being "actively wrong" with current implementation, though it
does not seem to be something actively defined (there are no casts
defined for json type) but rather an artifact of how postgresql input
works.

CAST is something that should convert one type to another, in this case
a textual type to its "json value" representation and back.

'sometext'::text::json --> '"sometext"'

and 

'"sometext"'::json::text --> 'sometext'


the suggested "syntax check only" should only be done by the type i/o
functions json_in(cstring) and json_recv(internal) and not casts.

Casts should do "casting", that in PostgreSQL means type conversion


> We can have constructors for
> JSON, but they've got to be separate from the casting machinery.

Currently we do have constructors - json_in(csting) and
json_recv(internal)

These are the ones that should and do  check for correct syntax.


Some more confusiong examples for pondering on cast vs i/o functions
--------------------------------------------------------------------

Some of the difficulties of understanding and explaining what a json
type should be are exemplified in the following


hannu=# create table jtest(plaintext text, jsontext json); 
CREATE TABLE

hannu=# insert into jtest values('"A"','"A"');
hannu=# insert into jtest values('true','true');
hannu=# insert into jtest values('null','null');
hannu=# insert into jtest values(null,null);

hannu=# select row_to_json(jtest) from jtest;            row_to_json              

--------------------------------------{"plaintext":"\"A\"","jsontext":"A"}{"plaintext":"true","jsontext":true}{"plaintext":"null","jsontext":null}{"plaintext":null,"jsontext":null}
(4 rows)

hannu=# insert into jtest values('a','a');
ERROR:  invalid input syntax for type json
LINE 1: insert into jtest values('a','a');                                    ^
DETAIL:  line 1: Token "a" is invalid.



-- 
-------
Hannu Krosing
PostgreSQL Unlimited Scalability and Performance Consultant
2ndQuadrant Nordic
PG Admin Book: http://www.2ndQuadrant.com/books/