/******************/
/*** operator + ***/
/******************/

CREATE OR REPLACE 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$;

drop operator if exists + (text, character varying);

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

CREATE OR REPLACE 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$;

drop operator if exists + (text, text);

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

CREATE OR REPLACE 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$;

drop operator if exists + (character varying, text);

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

CREATE OR REPLACE 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$;

drop operator if exists + (character varying, character varying);

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


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

--where field ilike 123
create or replace function int_int_ilike(int, int)
  RETURNS BOOLEAN AS $$
  SELECT $1::text = $2::text;
$$ LANGUAGE SQL IMMUTABLE;

drop operator if exists ~~* (int, int);

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

--where field not ilike 123
create or replace function int_int_not_ilike(int, int)
  RETURNS BOOLEAN AS $$
  SELECT $1::text <> $2::text;
$$ LANGUAGE SQL IMMUTABLE;

drop operator if exists !~~* (int, int);

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

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

CREATE OR REPLACE FUNCTION uuid_and_varchar_eq(uuid, character varying)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $1 = $2::uuid;
$function$;

drop operator if exists = (uuid, character varying);

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

CREATE OR REPLACE FUNCTION int_and_varchar_eq(int, character varying)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    select $1 = $2::int;
$function$;

drop operator if exists = (int, character varying);

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