瀚高数据库
目录
环境
文档用途
详细信息
环境系统平台:Microsoft Windows (64-bit) 10版本:5.6.5文档用途
解决应用程序中SQL语句使用!~~匹配操作符时,遇到的错误:
ERROR: 42883: operator does not exist: integer !~~ integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
详细信息
一,测试准备
创建表并插入数据:
create TABLE oper_test(first_num int);
insert into oper_test values(234),(446),(467);
二,测试查询
select (446 !~~ first_num) newlist from oper_test;
三,解决方案:自定义操作符
CREATE OR REPLACE FUNCTION hgdb_catalog.intnlike(integer,integer)
RETURNS boolean AS $$
select $1::varchar !~~ $2::varchar;
$$
LANGUAGE sql strict;
ALTER FUNCTION intnlike(integer, integer)
OWNER TO highgo;
COMMENT ON FUNCTION intnlike(integer,integer) IS 'implementation of !~~ operator';create operator hgdb_catalog.!~~(
procedure = hgdb_catalog.intnlike,
leftarg = integer,
rightarg = integer
);
四,再次查询