From: | Steve Chavez <steve(at)supabase(dot)io> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Castable Domains for different JSON representations |
Date: | 2023-06-24 21:35:38 |
Message-ID: | CAGRrpzZKa+Gu91j1SOvN3tM1f-7Gh_w441c5nAX1QqdH3Q31Lg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello hackers,
Currently domain casts are ignored. Yet this would be very useful for
representing data in different formats such as json.
Let's take a tsrange as an example. Its json output by default:
select to_json('(2022-12-31 11:00, 2023-01-01 06:00)'::tsrange);
to_json
-----------------------------------------------------
"(\"2022-12-31 11:00:00\",\"2023-01-01 06:00:00\")"
We can refine its representation in a custom way as:
-- using a custom type for this example
create type mytsrange as range (subtype = timestamp, subtype_diff =
tsrange_subdiff);
create or replace function mytsrange_to_json(mytsrange) returns json as $$
select json_build_object(
'lower', lower($1)
, 'upper', upper($1)
, 'lower_inc', lower_inc($1)
, 'upper_inc', upper_inc($1)
);
$$ language sql;
create cast (mytsrange as json) with function mytsrange_to_json(mytsrange)
as assignment;
-- now we get the custom representation
select to_json('(2022-12-31 11:00, 2023-01-01 06:00)'::mytsrange);
to_json
--------------------------------------------------------------------------------------------------------------
{"lower" : "2022-12-31T11:00:00", "upper" : "2023-01-01T06:00:00",
"lower_inc" : false, "upper_inc" : false}
(1 row)
Although this works for this example, using a custom type requires
knowledge of the `tsrange` internals. It would be much simpler to do:
create domain mytsrange as range;
But casts on domains are currently ignored:
create cast (mytsrange as json) with function mytsrange_to_json(mytsrange)
as assignment;
WARNING: cast will be ignored because the source data type is a domain
CREATE CAST
Checking the code seems supporting this is a TODO? Or are there any other
concerns of why this shouldn't be done?
I would like to work on this if there is an agreement.
Best regards,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | Ahmed Ibrahim | 2023-06-24 23:50:51 | Inquiry/Help with pg_adviser (problem in index_create function for creating indexes) |
Previous Message | Steve Chavez | 2023-06-24 20:52:11 | Re: 'converts internal representation to "..."' comment is confusing |