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