PostgreSQL返回自定义复合类型的函数在jOOQ中查询异常的问题

数据库模式 #

create table person (
    id bigint primary key generated by default as identity,
    name varchar(50) not null,
    age int not null,
    unique(name)
);

create type dressing as (
    id bigint,
    name varchar(50),
    age int,
    costume varchar(50)
);

CREATE OR REPLACE FUNCTION merge_person(arr dressing[]) RETURNS SETOF dressing LANGUAGE plpgsql AS
$$
DECLARE x dressing;
BEGIN
    FOREACH x IN ARRAY arr LOOP
        return query insert into person(name, age) values (x.name, x.age) on conflict (name) do update set age=x.age returning id,name,age,x.costume;
    END LOOP;
    RETURN;
END;
$$;

问题一：selectFrom(Routines.mergePerson(records))触发列不存在错误 #

错误信息 #

org.jooq.exception.DataAccessException: SQL [select "merge_person"."merge_person" from "public"."merge_person"(cast(? as "public"."dressing"[]))]; ERROR: column merge_person.merge_person does not exist

同时生成的com.test.db.tables.records.MergePersonRecord类中setMergePerson(Object value)方法存在“Unknown data type”警告。

原因 #

• jOOQ对返回SETOF复合类型的函数生成的Routine对象，被selectFrom错误解析为表结构，尝试选择与函数同名的列，而实际上函数返回的是复合类型集合，并非包含同名列的表。
• 代码生成阶段未正确识别PostgreSQL的dressing复合类型，导致生成的MergePersonRecord无法正确映射复合类型字段，出现类型警告。

解决方法 #

1. 修正代码生成配置：确保代码生成器包含PostgreSQL复合类型的解析，在生成配置中添加对复合类型的支持（比如在generator配置里设置database.includes包含dressing类型，确保复合类型被生成对应的DressingRecord类）。
2. 调整查询写法：避免直接用selectFrom调用Routine，改用select配合TABLE函数语法将函数作为表源，再映射到DressingRecord，示例：

List<DressingRecord> result = create.select()
    .from(DSL.table(Routines.mergePerson(records)))
    .fetchInto(DressingRecord.class);

问题二：select(DSL.asterisk()).from(Routines.mergePerson(records))仅返回id字段 #

现象 #

数据成功保存，但返回结果仅显示id字段：

+------------+
|merge_person|
+------------+
|1           |
|2           |
+------------+

期望返回完整的DressingRecord类型结果。

原因 #

PostgreSQL返回SETOF复合类型时，直接使用*会将整个复合类型视为单一列，jOOQ默认仅提取该复合列的第一个字段（id），未完整解析复合类型的所有属性。

解决方法 #

显式指定复合类型的每个字段进行查询，再映射到DressingRecord：

List<DressingRecord> result = create.select(
    DSL.field("t.id", Long.class),
    DSL.field("t.name", String.class),
    DSL.field("t.age", Integer.class),
    DSL.field("t.costume", String.class)
)
.from(DSL.table(Routines.mergePerson(records)).as("t"))
.fetchInto(DressingRecord.class);

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