PostgreSQL自定义类型转换

示例以如下两个自定义类型转换：

drop table if exists test;
drop cast if exists (__hoperate as __operate);
drop cast if exists (__operate as __hoperate);
drop function if exists conv_hoperate_to_operate(__hoperate);
drop function if exists conv_hoperate_from_operate(__operate);
drop type if exists __hoperate;
drop type if exists __operate;

create type __operate as (
	uid bigint,
	gen timestamptz
);

create type __hoperate as (
	uid bigint,
	gen timestamptz,
	action integer
);

1 创建转换函数

drop cast if exists (__hoperate as __operate);
drop cast if exists (__operate as __hoperate);
drop function if exists conv_hoperate_to_operate(__hoperate);
drop function if exists conv_hoperate_from_operate(__operate);


create function conv_hoperate_to_operate(
	iparam __hoperate
) returns __operate
as $$
	select ((iparam).uid,(iparam).gen)::__operate;
$$ language sql immutable strict;

create function conv_hoperate_from_operate(
	iparam __operate
) returns __hoperate
as $$
	select ((iparam).uid,(iparam).gen,0)::__hoperate;
$$ language sql immutable strict;

2 创建转换

create cast (__hoperate as __operate) with function conv_hoperate_to_operate(__hoperate) as assignment;
create cast (__operate as __hoperate) with function conv_hoperate_from_operate(__operate) as assignment;

3 转换测试

with cte as(
	select (1::bigint,now())::__operate as x1,(1::bigint,now(),1)::__hoperate as x2
)select 
	x1::__hoperate,cast(x1 as __hoperate),
	x2::__operate,cast(x2 as __operate) 
from cte;

4 表字段使用自定义并且要转换为其它自类型

如果没有定义转换函数，会报以下错误

create table test(
	id bigint,
	ope __hoperate
);

--删除转换修改表类型报错
drop cast if exists (__hoperate as __operate);
drop cast if exists (__operate as __hoperate);
drop function if exists conv_hoperate_to_operate(__hoperate);
drop function if exists conv_hoperate_from_operate(__operate);

alter table test alter column ope type __operate using cast(ope as __operate);

ERROR:  cannot cast type __hoperate to __operate
第1行... table test alter column ope type __operate using cast(ope a...

如果我们按1、2步骤创建了转换，那么执行表类型转换就没有问题了。
再次执行字段类型转换成功。

alter table test alter column ope type __operate using cast(ope as __operate);