jooqjooq-codegen

Can't select from postgres function which returns user-defined custom type


I have problem selecting from postgres function which returns setof user-defined composite type. I created a MRE project here: https://github.com/sify21/testjooq

my schema

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

Firstly, I can't use create.selectFrom(Routines.mergePerson(records)), it reports this error

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

I don't know where select "merge_person"."merge_person" comes from, apparently merge_person returns dressing record. I checked the generated code, com.test.db.tables.records.MergePersonRecord has a warning on setMergePerson(Object value) method, saying it's Unknown data type, but I don't know how to fix it.

Secondly, if I use create.select(DSL.asterisk()).from(Routines.mergePerson(records)), the records are saved in postgres, but the returned result only contains id field, this is the output

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

But it is not what I want, I want it to return DressingRecord instead


Solution

  • This is a known limitation of jOOQ 3.19, fixed in jOOQ 3.20. See:

    A workaround is to use an auxiliary table type instead of a user defined type:

    CREATE TABLE dressing_t AS (
        id bigint,
        name varchar(50),
        age int,
        costume varchar(50)
    );
    
    CREATE OR REPLACE FUNCTION merge_person(arr dressing[]) 
    RETURNS SETOF dressing_t 
    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;
    $$;