From: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: DOMAINs and CASTs
Date: 2011-05-14 18:28:14
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


If i create a DOMAIN an then want to create a CAST from that domain to
another type it gives an error.
Consider this example:
create domain datetime as timestamp with time zone
check (value between '1753-01-01 00:00:00' and '9999-12-31 23:59:59');

create function datetime2int(datetime) returns int
language sql stable strict as $$
select $1::date - '1753-01-01'::date;

create cast(datetime as int) with function datetime2int(datetime);

if i try to cast, get this error:
select now()::datetime::int;
ERROR: cannot cast type datetime to integer

The problem is that in find_coercion_pathway() the very first thing we
do is to get the base type of both: the source and target types. So,
the way to make it work is to create the function and the cast on the
base types.
But what if i create 2 domains on the same base types and want a
different behaviour on a cast to the same target type?

ok, sounds odd... basic example datetime and smalldatetime types in ms
sql server... when casting to int the former give the number of days
since 1753-01-01 and the latter the number of days since 1900-01-01...
some systems i have seen (specially ERPs) tend to store dates as
number of days so there is a use case for this.

the fix for this doesn't look complicated (unless have missed
something), just try first with the types i receive and then with the
base types if they are domains... i'm not trying mixed situations: the
base type of the source and the target as we receive it and viceversa,
i think that's just complicating for a very little benefit if any...

attached (pass all regression tests), comments?

Jaime Casanova
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

Attachment Content-Type Size
cast_domains.patch text/x-patch 1.9 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Mitsuru IWASAKI 2011-05-14 18:54:31 Re: patch for new feature: Buffer Cache Hibernation
Previous Message Jeff Janes 2011-05-14 17:33:36 Re: Reducing overhead of frequent table locks