CODEWITHSUNDEEP
sqlpostgresqloperator-overloadingplpgsql
Andrus
Andrus

Reputation: 27955

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?

Upvotes: 0

Views: 49

Answers (1)

Belayer
Belayer

Reputation: 14934

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

Upvotes: 1

Related Questions