Re: json casts

On 05/27/2014 05:43 PM, Hannu Krosing wrote:
> On 05/27/2014 11:00 PM, Tom Lane wrote:
>>>
>>> See src/backend/utils/adt/json.c:json_categorize_type() lines 1280-1300.
>>> When rendering some value as part of a json string, if a cast exists
>>> from the data type to json, then the cast function is used to render the
>>> json instead of the type's normal output function, but only if it's not
>>> a builtin type.
>> How exactly would disabling that code have any effect on timestamp
>> rendering? There's no cast to json from timestamps (nor any other
>> builtin type, except jsonb).
> I think Andrews idea was, that if cast were used, one could fix the above
> problem by defining a correct cast.

Right.

>
>
>> I'd be inclined to think a more useful answer to this issue would be to
>> make json.c special-case timestamps, as it already does for numerics.
>>
>>

OK, that's another approach.

> But I agree that special-casing the code to use the de-facto json standard
> of using ISO 8601 date representation is a better solution.
>
> Just make sure you get the TZ part right - this is another place where
> PostgreSQL often differs from other systems' understanding of ISO
> timestamps.

The target would be something like:

to_char($1,'\"YYYY-MM-DD"T"HH:MI:SS.USOF\"')

AIUI that should be legal ISO 8601. But I'm happy to defer to experts.

Given that this would be a hard coded behaviour change, is it too late
to do this for 9.4?

cheers

andrew