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
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 |