Обсуждение: domain type with create cast not working on pg15, but work on pg14

Поиск
Список
Период
Сортировка

domain type with create cast not working on pg15, but work on pg14

От
alias
Дата:
seems like a bug, since PostgreSQL 15 - pgPedia - a PostgreSQL Encyclopedia does not mention anything about domain/cast changes. 

the following code works on
 PostgreSQL 15devel (Ubuntu 15~~devel~20220407.0430-1~713.git79b716c.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
 ---
create domain isodow as integer check(value between 1 and 7);
create function testcast(in a timestamptz ,out isodow)
as $$ select extract(dow from  a ) $$ language  sql;
CREATE CAST (timestamptz AS isodow) WITH FUNCTION testcast(timestamptz) AS ASSIGNMENT;
----
then execute it:
   select '2022-04-23 19:00:01 +5:30'::timestamptz::isodow; 
return 6
---
however when i run it  PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

yield the following error:
ERROR:  42846: cannot cast type timestamp with time zone to isodow
LINE 1: select '2022-04-23 19:00:01 +5:30'::timestamptz::isodow;
                                                       ^
LOCATION:  transformTypeCast, parse_expr.c:2652

 

Re: domain type with create cast not working on pg15, but work on pg14

От
Tom Lane
Дата:
alias <postgres.rocks@gmail.com> writes:
> create domain isodow as integer check(value between 1 and 7);
> create function testcast(in a timestamptz ,out isodow)
> as $$ select extract(dow from  a ) $$ language  sql;
> CREATE CAST (timestamptz AS isodow) WITH FUNCTION testcast(timestamptz) AS
> ASSIGNMENT;

For me, on either v14 or v15 that draws an informative warning:

WARNING:  cast will be ignored because the target data type is a domain

and that makes it unsurprising that this fails:

=# select '2022-04-23 19:00:01 +5:30'::timestamptz::isodow;
ERROR:  cannot cast type timestamp with time zone to isodow
LINE 1: select '2022-04-23 19:00:01 +5:30'::timestamptz::isodow;
                                                       ^

I don't see any difference in behavior between v13, v14, v15
on this.  The test case fails outright before v13, because
older versions wouldn't do the implied cast here:

=# create function testcast(in a timestamptz ,out isodow)
as $$ select extract(dow from  a ) $$ language  sql;
ERROR:  return type mismatch in function declared to return isodow
DETAIL:  Actual return type is double precision.
CONTEXT:  SQL function "testcast"

but with that corrected, the warning appears at least back to v10.

            regards, tom lane