Re: data to json enhancements - Mailing list pgsql-hackers

On 09/27/2012 09:18 PM, Andrew Dunstan wrote:
>
> On 09/27/2012 10:36 AM, Tom Lane wrote:
>> Andrew Dunstan <andrew@dunslane.net> writes:
>>> On 09/27/2012 09:22 AM, Robert Haas wrote:
>>>> Maybe I am being too pedantic about this and there is a way to make it
>>>> all work nicely, but it sure feels like using the casting machinery
>>>> here is blending together two different concepts that are only
>>>> sometimes the same.
>>> OK. I think that's a very good point. I guess I was kinda swept away by
>>> this being suggested by a couple of influential people.
>> Well, that doesn't make it wrong, it just means there's more work
>> needed.  I'm not that thrilled with magic assumptions about function
>> names either; schema search path issues, for example, will make that
>> dangerous.  We've gone to considerable lengths to avoid embedding
>> assumptions about operator names, and assumptions about function names
>> aren't any better.
>>
>> There are at least three ways we could use the cast machinery for this:
>>
>> (1) Reject Robert's assumption that we have to support both
>> interpretations for every cast situation.  For instance, it doesn't
>> seem that unreasonable to me to insist that you have to cast to text
>> and then to json if you want the literal-reinterpretation behavior.
Maybe cast not to text but to cstring for getting the text-is-already-json ?

That is, reuse the current type io as "literal" casts.

This way a cast of '{"a": 1}'::json::text will fail, as this json value 
really does not
represent a text/string value.

>> The main problem then is figuring out a convenient way to provide
>> interpretation #2 for text itself.
>
>
> The trouble is, ISTM, that both things seem equally intuitive. You 
> could easily argue that x::text::json means take x as text and treat 
> it as json, or that it means take x as text and produce a valid json 
> value from it by escaping and quoting it. It's particularly ambiguous 
> when x is itself already a text value. If we go this way I suspect 
> we'll violate POLA for a good number of users.
It may be easier to sort this out if we think in terms of symmetry and 
unambiguity.

let's postulate that mytype::json::mytype and json::mytype::json should 
always reproduce the original result or they should fail.

so '[1,2,3]'::text::json::text === '[1,2,3]'::text with intermediate  
json being '"[1,2,3]"'

and '[1,2,3]'::json::text::json fails the json-->text casts as 
'[1,2,3]'::json does not represent
a text value (in a similar way as '[1,2,3]'::json::date fails)

on the other hand '[1,2,3]'::json::int[]::json should succeed as there 
is a direct mapping to int array.

....

>> (3) Invent an auxiliary type along the lines of "json_value" and say
>> that you create a cast from foo to json_value when you want one
>> interpretation, or directly to json if you want the other.  Then
>> things like record_to_json would look for the appropriate type of cast.
>> This is a bit ugly because the auxiliary type has no reason to live
>> other than to separate the two kinds of cast, but it avoids creating
>> any new JSON-specific mechanisms in the type system.
As suggested above, this special type could be on the other side - the 
type cstring as
already used for type io functions

the main problem here is, that currently we do interpret ::text::json as it
were the type input function.

we do proper selective quoting when converting to back json

hannu=# create table jt(t text, j json);
CREATE TABLE
hannu=# insert into jt values ('[1,2]','[3,4]');
INSERT 0 1
hannu=# select row_to_json(jt) from jt;       row_to_json
------------------------- {"t":"[1,2]","j":[3,4]}
(1 row)

but we do automatic casting through cstring and json type input func 
when converting to json.

hannu=# select t::json, j::json from jt;   t   |   j
-------+------- [1,2] | [3,4]
(1 row)

This should probably be cleaned up.

>
> I could accept this. The reason is that very few types are in fact 
> going to need a gadget like this. Yes it's mildly ugly, but really 
> fairly unobtrusive.
>
> cheers
>
> andrew
>
>>
>> There might be some other ideas I'm not thinking of.
>
>
> Yeah. You've done better than me though :-)
>
> cheers
>
> andrew
>