Postgres Pro
Downloads
Home   >   mailing lists

[ADMIN] custom cast for to_json() - Mailing list pgsql-admin

From Raj Gandhi
Subject [ADMIN] custom cast for to_json()
Date
Msg-id CALU_HCNdb5Dh2rb7o6VdRxr+8J3xnJnAhJEo0u_+RjmvpdYtxQ@mail.gmail.com
Whole thread Raw
List pgsql-admin
Tree view
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

pgsql-admin by date:

Previous
From: Michal Merta
Date:
Subject: [ADMIN] Amount of unused space on a storage device
Next
From: Marco Piovan
Date:
Subject: [ADMIN] monitor create index concurrently