PostgreSQL使用复合类型ltree的plpgsql函数执行时报integer输入语法错误

核心问题由两点共同导致：

1. 自定义类型与官方扩展重名冲突：你在public schema下自定义了名为ltree的复合类型，但PostgreSQL官方自带ltree扩展（多数发行版默认预装，部分环境默认启用），该扩展的ltree是存储树形路径的文本类型，和你自定义的二元组复合类型完全不同。
2. 动态SQL的类型推断偏差：你单独执行select l_tree from parent_tree where parent_id = 1;能正常返回，是因为静态查询会自动识别表字段的自定义类型；但函数里用EXECUTE执行的动态SQL是运行时解析类型，重名情况下系统优先匹配了官方ltree类型，尝试把你的二元组值(2,2)解析为官方ltree格式时触发类型转换报错。

方案1：规避重名（推荐） #

直接修改自定义类型名称，从根源避免和官方类型冲突，后续也不会出现其他隐式问题：

-- 清理原有冲突对象
DROP FUNCTION IF EXISTS public.get_parent_ltree(int, regclass);
DROP TABLE IF EXISTS public.parent_tree;
DROP TYPE IF EXISTS public.ltree;

-- 重命名自定义类型后重建
begin;
create type public.my_tree as (a int, b int);
create table public.parent_tree(parent_id int,l_tree my_tree);
insert into public.parent_tree values(1,(2,2)),(2,(1,2)),(3, (1,28));
commit;

-- 同步修改函数的类型声明
CREATE OR REPLACE FUNCTION public.get_parent_ltree
            (_parent_id int, tbl_name regclass , OUT _l_tree my_tree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT l_tree FROM %s WHERE parent_id = $1', tbl_name)
   INTO  _l_tree
   USING _parent_id;
END
$func$;

方案2：显式指定自定义类型（不改重名） #

如果不想调整现有类型名，给所有用到自定义ltree的位置加上schema前缀，强制指定用你自己创建的复合类型即可：

CREATE OR REPLACE FUNCTION public.get_parent_ltree
            (_parent_id int, tbl_name regclass , OUT _l_tree public.ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT l_tree::public.ltree FROM %s WHERE parent_id = $1', tbl_name)
   INTO  _l_tree
   USING _parent_id;
END
$func$;

修复后调用即可正常返回结果：

select get_parent_ltree(1,'public.parent_tree');
-- 返回结果：(2,2)

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