postgresql 函数实现-数据类型转换实例

postgresql 函数实现-数据类型转换实例

最近小崔在项目上做迁移遇到了一个很棘手的数据类型转换问题，今天给大家分享下。

一. integer 转 boolean

问题描述：客户使用hibernate自动生成sql语句，其中有一段Sql如下：

select userdepart0_.ID as ID251_, userdepart0_.CREATE_TIME as CREATE2_251_, userdepart0_.MODIFY_TIME as MODIFY3_251_, userdepart0_.DEPARTMENT_ID as DEPARTMENT7_251_, userdepart0_.ORDER_NUMBER as ORDER4_251_, userdepart0_.PLURALITY as PLURALITY251_, userdepart0_.TYPE as TYPE251_, userdepart0_.USER_ID as USER8_251_

from table1 userdepart0_

where userdepart0_.PLURALITY=false and userdepart0_.USER_ID='123123112312'；

这个sql 在执行的时候就会报：

ERROR:  错误:  操作符不存在: integer = boolean

LINE 3: where userdepart0_.PLURALITY=false and userdepart0_.USER_ID=...

                                    ^

HINT:  没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.

SQL 状态:42883

字符: 384

查看表中的数据类型：

这里的问题就是查询的数据类型boolean型和对应的数据库中integer类型不匹配。所以需要将数据类型进行转换

具体操作：

 创建转换function：

CREATE OR REPLACE FUNCTION "PUBLIC".integer_eq_boolean(integer, BOOLEAN) RETURNS BOOLEAN

    AS $$

  begin

    select $1 = $2::int;

  end

$$

language pluxsql;

ALTER FUNCTION "PUBLIC".integer_eq_boolean(integer, BOOLEAN) OWNER TO uxdb;

定义运算符：

CREATE OPERATOR "PUBLIC".= (

    PROCEDURE = "integer_eq_boolean",

    LEFTARG = integer,

    RIGHTARG = BOOLEAN

);

ALTER OPERATOR "PUBLIC".= (integer, BOOLEAN) OWNER TO "uxdb";

再次执行sql及正常：

二. 多种类型转换

问题描述：sql第二种情况：

select roleassign0_.ROLE_ID as col_0_0_ from table2 roleassign0_

where roleassign0_.INCLUDE_CHILDREN=1 and (roleassign0_.ORG_TYPE='UNIT' or roleassign0_.ORG_TYPE='DEPARTMENT')

and (roleassign0_.ASSIGN_ID in ('909499817214364035')) and roleassign0_.APPLICATION_ID=1000000000；

错误:  操作符不存在: character varying = integer

第2行where roleassign0_.INCLUDE_CHILDREN=1 and (roleassign0_.ORG_...

                                        ^

提示:  没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.

查看表中字段的数据类型：

这个值传入的是1 ，实际的数据库类型是varchar，所以这样的话需要创建一个函数去做转换。

CREATE OR REPLACE FUNCTION "PUBLIC".varying_eq_integer(

    character varying,

    integer)

    RETURNS boolean

    LANGUAGE 'pluxsql'

    COST 100

    VOLATILE

AS $BODY$

  begin

   return $1::int = $2;     --把第一个值转成integer

  end

$BODY$;

ALTER FUNCTION "PUBLIC".varying_eq_integer(character varying, integer) OWNER TO cetc15;

CREATE OPERATOR "PUBLIC".= (

    PROCEDURE = "varying_eq_integer",

    LEFTARG = character varying,

    RIGHTARG = integer

);

ALTER OPERATOR "PUBLIC".= (character varying,integer) OWNER TO "cetc15";

重新跑sql语句

    select roleassign0_.ROLE_ID as col_0_0_ from table2 roleassign0_

    where roleassign0_.INCLUDE_CHILDREN=1 and (roleassign0_.ORG_TYPE='UNIT' or roleassign0_.ORG_TYPE='DEPARTMENT')

    and (roleassign0_.ASSIGN_ID in ('909499817214364035')) and roleassign0_.APPLICATION_ID=1000000000；

错误:  无效的整数类型输入语法: "UNIT"

第2行...n0_.INCLUDE_CHILDREN=1 and (roleassign0_.ORG_TYPE='UNIT' or ...

虽然保证了varchar转换成了integer，但是在第二个地方varchar也跟着转成了integer，但是这个字段是个varchar类型。

为了解决这个问题，需要将varchar对应的转成varchar型。

这里要注意，pg里不支持varchar->varchar，但是可以改成text->text，具体方法如下：

CREATE OR REPLACE FUNCTION "PUBLIC".varchar_eq_varchar(

    character varying,

    character varying)

    RETURNS boolean

    LANGUAGE 'pluxsql'

    COST 100

    VOLATILE

AS $BODY$

  begin

    return $1::text = $2::text;     --这里两次转换都是text

  end

$BODY$;

ALTER FUNCTION "PUBLIC".varchar_eq_varchar(character varying, character varying)

    OWNER TO cetc15;

CREATE OPERATOR "PUBLIC".= (

    PROCEDURE = "varchar_eq_varchar",

    LEFTARG = character varying,

    RIGHTARG = character varying

);

ALTER OPERATOR "PUBLIC".= (character varying,character varying) OWNER TO "cetc15";

再次执行sql：

成功

postgresql 可以通过修改数据库内部内置的转换法则将其开启，但是未必好用，一但无法实现，还是需要使用function转换的方法去进行修改。方法连接如下：https://yq.aliyun.com/articles/424712

THAT'S ALL

BY CUI PEACE