Skip site navigation (1) Skip section navigation (2)


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
Message-ID: (view raw, whole thread or download thread mbox)
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: cast_domains.patch
Description: text/x-patch (1.9 KB)


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group