PostgreSQL中如何实现不存在则创建运算符？

问题场景 #

执行以下SQL代码时，首次运行正常，但重复执行会抛出异常：

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

异常信息：

ERROR: operator + already exists

解决方案 #

PostgreSQL没有直接的CREATE OR REPLACE OPERATOR语法，但可以通过条件判断+动态SQL实现“不存在则创建”的逻辑，具体实现如下：

1. 保留函数的可重复创建逻辑 #

函数部分已经使用CREATE OR REPLACE，多次执行不会报错，这部分无需修改。

2. 新增运算符的存在性检查逻辑 #

借助pg_operator系统表判断目标运算符是否存在，结合DO块执行动态创建语句：

CREATE OR REPLACE FUNCTION public.concatkeepspaces(left bpchar, right bpchar)
RETURNS bpchar
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT concat($1,$2);
$BODY$;

DO $$
BEGIN
  -- 检查public模式下，针对bpchar类型的+运算符是否已存在
  IF NOT EXISTS (
    SELECT 1 FROM pg_operator
    WHERE oprname = '+'
      AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'bpchar')
      AND oprright = (SELECT oid FROM pg_type WHERE typname = 'bpchar')
      AND oprnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
  ) THEN
    -- 不存在则执行创建运算符的动态SQL
    EXECUTE '
      CREATE OPERATOR public.+ (
        leftarg = bpchar,
        rightarg = bpchar,
        procedure = public.concatkeepspaces
      );
    ';
  END IF;
END $$;

逻辑说明 #

• pg_operator是PostgreSQL存储所有运算符元数据的系统表，通过oprname（运算符名称）、oprleft/oprright（左右操作数类型的OID）、oprnamespace（所属模式的OID）可以精准定位目标运算符。
• 使用DO块执行匿名PL/pgSQL代码，通过IF NOT EXISTS做存在性判断，仅在运算符不存在时执行CREATE OPERATOR语句，避免重复创建报错。

内容的提问来源于stack exchange，提问作者Andrus