[金仓数据库V8R6]bit与boolean隐式转换

创建隐式转换后，执行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');