/******************/
/*** 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;

DROP CAST IF EXISTS (character varying AS uuid);
CREATE CAST (character varying AS uuid) WITH INOUT AS IMPLICIT;
DROP CAST IF EXISTS (text AS uuid);
CREATE CAST (text AS uuid) WITH INOUT AS IMPLICIT;

DROP FUNCTION IF EXISTS int_and_varchar_eq CASCADE;

DROP CAST IF EXISTS (character varying AS integer);
CREATE CAST (character varying AS integer) WITH INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS integer);
CREATE CAST (text AS integer) WITH INOUT AS IMPLICIT;

DROP CAST IF EXISTS (character varying AS double precision);
CREATE CAST (character varying AS double precision) WITH INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS double precision);
CREATE CAST (text AS double precision) WITH INOUT AS IMPLICIT;


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 $2 = $1::boolean;
$function$;

drop operator if exists = (int, boolean);

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 $1 = $2::boolean;
$function$;

drop operator if exists = (boolean, int);

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