Hi,
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 www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 0418972..7101499 100644
*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
*************** find_coercion_pathway(Oid targetTypeId,
*** 1873,1885 ****
*funcid = InvalidOid;
! /* Perhaps the types are domains; if so, look at their base types */
! if (OidIsValid(sourceTypeId))
! sourceTypeId = getBaseType(sourceTypeId);
! if (OidIsValid(targetTypeId))
! targetTypeId = getBaseType(targetTypeId);
!
! /* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
--- 1873,1879 ----
*funcid = InvalidOid;
! /* If they are the same type will always be coercible */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
*************** find_coercion_pathway(Oid targetTypeId,
*** 1888,1893 ****
--- 1882,1913 ----
ObjectIdGetDatum(sourceTypeId),
ObjectIdGetDatum(targetTypeId));
+ if (!HeapTupleIsValid(tuple))
+ {
+ Oid sourceBaseTypeId;
+ Oid targetBaseTypeId;
+
+ /* Perhaps the types are domains; if so, look at their base types */
+ if (OidIsValid(sourceTypeId))
+ sourceBaseTypeId = getBaseType(sourceTypeId);
+ if (OidIsValid(targetTypeId))
+ targetBaseTypeId = getBaseType(targetTypeId);
+
+ if ((sourceBaseTypeId != sourceTypeId) || (targetBaseTypeId != targetTypeId))
+ {
+ sourceTypeId = sourceBaseTypeId;
+ targetTypeId = targetBaseTypeId;
+
+ /* Domains are always coercible to and from their base type */
+ if (sourceTypeId == targetTypeId)
+ return COERCION_PATH_RELABELTYPE;
+
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(sourceTypeId),
+ ObjectIdGetDatum(targetTypeId));
+ }
+ }
+
if (HeapTupleIsValid(tuple))
{
Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
