Thread: 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

From

alias

Date:

25 April 2022, 09:07:49

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

From

Tom Lane

Date:

25 April 2022, 16:47:25

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