Re: to_json(now()) result doesn't have 'T' separator

On Mon, Dec 23, 2013 at 5:37 PM, Joe Van Dyk <joe@tanga.com> wrote:
> I tried making some cast functions as follows, but it doesn't seem to work
> properly:
>
> create or replace function iso_timestamp(timestamptz) returns json as $$
>   select ('"' ||
>           substring(xmlelement(name x, $1)::text from 4 for 32) || '"'
>         )::json
> $$ language sql immutable;
>
> create cast (timestamptz as json) with function iso_timestamp (timestamptz)
> as implicit;
>
> create function to_json(timestamptz) returns json as $$
>   select $1::json
> $$ language sql immutable;
>
> create table t (id serial primary key, created_at timestamptz default
> now());
> insert into t values (default);
> select row_to_json(t) from t;
>
>                       row_to_json
> -------------------------------------------------------
>  {"id":1,"created_at":"2013-12-23 17:37:08.825935-08"}
>
>


Close as I could get using above is:

test=# select to_json(created_at) from t;
              to_json
------------------------------------
 "2013-12-23T19:09:33.886092-08:00"

Seems row_to_json bypasses casts whereas to_json does not.
--
Adrian Klaver
adrian.klaver@gmail.com