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);