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

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Hannu Krosing
> Sent: Friday, May 04, 2012 4:40 PM
> To: Robert Haas
> Cc: Tom Lane; Andrew Dunstan; PostgreSQL-development; Merlin Moncure
> Subject: Re: [HACKERS] JSON in 9.2 - Could we have just one to_json()
> function instead of two separate versions ?
>
> 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.  We can have constructors for
> > JSON, but they've got to be separate from the casting machinery.
>
> on the contrary - the string representation of textual value a is "a"
>
> casting should _not_ neam syntax check, casting is by definition a conversion.
>
> if we cast text to int, we return value of type int , if we cast int to
> numeric(5,2) we return value of type numeric(5,2)
>
> why should casring to json work differntly ?
>

What is the distinction between what you are thinking regarding JSON and this example?

SELECT '1a'::integer;
SQL Error: ERROR:  invalid input syntax for integer: "1a"
LINE 1: SELECT '1a'::integer

As a user if I cast something to something else I want the result to be of the correct type and deterministic;
otherwisethrow me some kind of "invalid input format" exception (or syntax exception).  Casting vs. Constructors is
reallya meaningless distinction to a lay person.  When I cast I do so by constructing a new value using my existing
valuefor input.  When I use an explicit CAST I am unable to supply additional parameters to configure the casting
whereasa constructor function gives me that possibility.  But a constructor function without any additional parameters
isnot semantically different than a cast. 

I guess the concern to address is something like:

SELECT '{key: value}'::json OR SELECT '[1.25]'::json;  Do you interpret this as already being valid JSON and thus
outputobject/array constructs (i.e., JSON Text) or do you treat them as string literals and output scalars (i.e., JSON
Value). Even if you feel these are artificial constructs the concepts holds that there may be ambiguous data that can
beinterpreted in multiple ways (this applies even to function forms, though in the function form you could specify
whichone you want to output using a separate DEFAULTed parameter).  



I can see the primary use-case for JSON Value casting as being queries of the following forms (since the record and
arrayforms are going through the record/array_to_json function): 

SELECT COUNT(*)::json FROM table [WHERE ...];
SELECT single_col::json FROM table WHERE id = ?;

Where the single provided value can be sent directly back to the web-caller JavaScript and used as-is because it is
validJSON.  Though, honestly, both SELECT to_json(single_col) and SELECT to_json(COUNT(*)) are equally usable so any
distinctionbetween them is a pure technical issue to me. 

Am I correct in assuming the following expected behavior (the forgive the blatantly wrong syntax but you should get the
point)?

RAISE NOTICE '%', SELECT 'A'::text  =>  A
RAISE NOTICE '%', SELECT 'A'::json => "A"

David J