custom cast for to_json()

From: Raj Gandhi <raj01gandhi(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: custom cast for to_json()
Date: 2017-09-01 02:27:31
Message-ID: CALU_HCNdb5Dh2rb7o6VdRxr+8J3xnJnAhJEo0u_+RjmvpdYtxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Marco Piovan 2017-09-01 13:09:24 monitor create index concurrently
Previous Message Michal Merta 2017-08-31 14:52:05 Amount of unused space on a storage device