I can confirm that this is a bug. The attached SQL shows that creating
a CAST _to_ a domain type doesn't work, though the cast can be created.
The attached SQL provided by Fabien shows the failure.
The error is coming from parse_expr.c::typecast_expression, and its call
to typenameTypeId(). I wish I understood how we do domains better to
fix this properly. Anyone?
---------------------------------------------------------------------------
Fabien COELHO wrote:
>
> Dear PostgreSQL developer.
>
> Although it is allowed to create a cast for a domain, it seems that there
> is no way to trigger it. You can find attached an sql script to illustrate
> the issue with postgresql 8.1.3. The create cast and create domain
> documentations do not seem to discuss this point.
>
> ISTM that it is a pg bug. Indeed, either
>
> (1) the create cast should be rejected if it is not allowed for domains.
>
> or
>
> (2) the function should be triggered by explicit casts to the domain.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
DROP DOMAIN a_year CASCADE;
-- a simple domain
CREATE DOMAIN a_year AS INTEGER
CHECK (VALUE BETWEEN 1 AND 3000);
-- ok
SELECT 1::a_year;
SELECT CAST('2000' AS a_year);
-- fails as expected
SELECT 0::a_year;
CREATE FUNCTION date2year(DATE)
RETURNS a_year IMMUTABLE STRICT AS $$
SELECT EXTRACT(YEAR FROM $1)::a_year;
$$ LANGUAGE sql;
-- ok
SELECT date2year(CURRENT_DATE);
-- fails as expected
SELECT date2year(DATE '3001-01-01');
CREATE CAST (DATE AS a_year)
WITH FUNCTION date2year(DATE);
-- fails, I would expect 1970
SELECT (DATE '1970-03-20')::a_year;
-- fails, I would expect the current year
SELECT CURRENT_DATE::a_year;
SELECT CAST(CURRENT_DATE AS a_year);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
