创建隐式转换后,执行SQL时可以自动去转换对应的数据类型而不用去修改SQL转换对应的类型了。
1、创建bit与boolean隐式转换
\set SQLTERM / CREATE OR REPLACE FUNCTION boolean_to_bit(b boolean) RETURNS bit AS $$ BEGIN RETURN b::int::bit; END; $$LANGUAGE plpgsql; / CREATE OR REPLACE FUNCTION bit_to_boolean(b bit) RETURNS boolean AS $$ BEGIN RETURN b::int::boolean; END; $$LANGUAGE plpgsql; / \set SQLTERM ; CREATE CAST (bit AS boolean) WITH FUNCTION bit_to_boolean(bit) AS implicit; CREATE CAST (boolean AS bit) WITH FUNCTION boolean_to_bit(boolean) AS implicit;
2、查询隐式转换
SELECT a.oid,a.CASTCONTEXT FROM pg_cast a WHERE castsource=(SELECT oid FROM pg_type WHERE UPPER(TYPNAME)='BOOL') AND CASTTARGET IN (SELECT OID FROM pg_type WHERE UPPER(TYPNAME)='BIT'); SELECT a.oid,a.CASTCONTEXT FROM pg_cast a WHERE castsource=(SELECT oid FROM pg_type WHERE UPPER(TYPNAME)='BIT') AND CASTTARGET IN (SELECT OID FROM pg_type WHERE UPPER(TYPNAME)='BOOL');