環境

系統平臺： 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
);

四，再次查詢

瀚高資料庫自定義操作符'!~~'

相關文章