custom cast for to_json()

[Raj Gandhi <raj01gandhi@xxxxxxxxx>]

Hello everyone,

I'm trying to use custom cast for to_json() function for timestamp type. We are using 3rd party tool which dynamically generates all SQLs so can't directly use to_char() in the SQL to format the timestamp. 

-- here is the user defined function to do the conversion from timestamp to json

CREATE or replace FUNCTION mytimecast(val timestamp with time zone) RETURNS json AS $$

BEGIN

RETURN to_json(to_char(val, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ'));

END; $$

LANGUAGE PLPGSQL;

--custom cast that uses the above user defined function 

create cast (timestamp with time zone AS json ) with function mytimecast (timestamp with time zone) AS IMPLICIT;

 

Direct cast to json uses the custom cast and returns the formatted date time from the user defined function:

select now()::json;

            now            

----------------------------

"2017-08-31T13:01:04.782Z"

 

 

However, to_json() function didn't use the custom cast and returning the default UTC format:

select to_json(now());

              to_json              

------------------------------------

"2017-08-31T13:01:18.474781+00:00"

Custom cast type should work based on the to_json() doc in the Postgres manual https://www.postgresql.org/docs/9.5/static/functions-json.html:

" if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced."

Did I miss something? How do I make to_json() to use the cast?

Thanks & Regards

Raj