Query
CREATE OR REPLACE FUNCTION public.concatkeepspaces(left bpchar, right bpchar)
RETURNS bpchar
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT concat($1,$2);
$BODY$;
CREATE OPERATOR public.+ (
leftarg = bpchar,
rightarg = bpchar,
procedure = public.concatkeepspaces
);
throws exeption if executed multiple times:
ERROR: operator + already exists
How to create operator if not exists is Postgres?
Well (imho) the best way is just create the operator outside the normal process and do so only once. But with a little subterfuge you can achieve what you're after. Instead of create operator
you create a procedure which then dynamically creates the operator. Additionally, the procedure traps the exception on subsequent calls and discards the error. The result being just like the error never occurred. Your normal process then just calls the procedure. (see demo)
create or replace procedure create_bpchar_plus_operator()
language plpgsql
as $$
begin
execute $create$
create operator public.+ ( leftarg = bpchar
, rightarg = bpchar
, procedure = public.concatkeepspaces
);
$create$;
exception
when sqlstate '42723' then null;
end;
$$;