/******************/
/*** operator + ***/
/******************/
DROP FUNCTION IF EXISTS concatenate_text_and_varchar CASCADE;
CREATE FUNCTION concatenate_text_and_varchar(text, character varying)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin
 return $1 || $2;
 exception when others 
  then return null::text;
end;
$function$;

CREATE OPERATOR + (
	PROCEDURE = concatenate_text_and_varchar
,	LEFTARG = text 
,	RIGHTARG = character varying
,	COMMUTATOR = +
);

DROP FUNCTION IF EXISTS concatenate_texts CASCADE;
CREATE FUNCTION concatenate_texts(text, text)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin
 return $1 || $2;
 exception when others 
  then return null::text;
end;
$function$;

CREATE OPERATOR + (
	PROCEDURE = concatenate_texts
,	LEFTARG = text 
,	RIGHTARG = text
,	COMMUTATOR = +
);

DROP FUNCTION IF EXISTS concatenate_varchar_and_text CASCADE;
CREATE FUNCTION concatenate_varchar_and_text(character varying, text)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin
 return $1 || $2;
 exception when others 
  then return null::text;
end;
$function$;

CREATE OPERATOR + (
	PROCEDURE = concatenate_varchar_and_text
,	LEFTARG = character varying
,	RIGHTARG = text
,	COMMUTATOR = +
);

DROP FUNCTION IF EXISTS concatenate_varchar_and_varchar CASCADE;
CREATE FUNCTION concatenate_varchar_and_varchar(character varying, character varying)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin
 return $1 || $2;
 exception when others 
  then return null::text;
end;
$function$;

CREATE OPERATOR + (
	PROCEDURE = concatenate_varchar_and_varchar
,	LEFTARG = character varying 
,	RIGHTARG = character varying
,	COMMUTATOR = +
);


/******************/
/* operator ilike */
/******************/

--where field ilike 123
DROP FUNCTION IF EXISTS int_int_ilike CASCADE;
CREATE FUNCTION int_int_ilike(int, int)
  RETURNS BOOLEAN AS $$
  SELECT $1::text = $2::text;
$$ LANGUAGE SQL IMMUTABLE;

create operator ~~* (leftarg = int, rightarg = int, procedure = int_int_ilike, commutator = ~~*, NEGATOR = !~~*);

--where field not ilike 123
DROP FUNCTION IF EXISTS int_int_not_ilike CASCADE;
CREATE FUNCTION int_int_not_ilike(int, int)
  RETURNS BOOLEAN AS $$
  SELECT $1::text <> $2::text;
$$ LANGUAGE SQL IMMUTABLE;

create operator !~~* (leftarg = int, rightarg = int, procedure = int_int_not_ilike, commutator = !~~*, NEGATOR = ~~*);

/******************/
/* operator = */
/******************/

DROP FUNCTION IF EXISTS uuid_and_varchar_eq CASCADE;
CREATE FUNCTION uuid_and_varchar_eq(uuid, character varying)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $1 = $2::uuid;
$function$;

CREATE OPERATOR = (
	PROCEDURE = uuid_and_varchar_eq
,	LEFTARG = uuid
,	RIGHTARG = character varying
);

DROP FUNCTION IF EXISTS int_and_varchar_eq CASCADE;
CREATE FUNCTION int_and_varchar_eq(int, character varying)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $1 = $2::int;
$function$;

CREATE OPERATOR = (
	PROCEDURE = int_and_varchar_eq
,	LEFTARG = int
,	RIGHTARG = character varying
);

DROP FUNCTION IF EXISTS int_and_boolean_eq CASCADE;
CREATE FUNCTION int_and_boolean_eq(int, boolean)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $1 = case when $2 then 1 else 0 end;
$function$;

CREATE OPERATOR = (
	PROCEDURE = int_and_boolean_eq
,	LEFTARG = int
,	RIGHTARG = boolean
);

DROP FUNCTION IF EXISTS boolean_and_int_eq CASCADE;
CREATE FUNCTION boolean_and_int_eq(boolean, int)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $2 = case when $1 then 1 else 0 end;
$function$;

CREATE OPERATOR = (
	PROCEDURE = boolean_and_int_eq
,	LEFTARG = boolean
,	RIGHTARG = int
);
