sqlpostgresqloperator-overloadingplpgsql

How to create operator if not exists


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?


Solution

  • 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;
    $$;