Cannot execute PostgreSQL function with Jooq

Question

Java 1.8, Jooq 3.10.3, Postgres 10.1

I created Postgres function which get table parameter. After JOOQ generate code I tried to call function from Java code but got strange exception.

Postgres part:

CREATE TABLE testtab
(
  id integer NOT NULL,
  txt character varying,
  CONSTRAINT testtab_pkey PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION public.testfunc(val testtab)
RETURNS void
LANGUAGE 'plpgsql'

AS //

begin
  insert into testtab select  * from val;
end;

//;

Java Part (TesttabRecord and Testfunc generated by Jooq codegen):

try (Connection conn = DriverManager.getConnection(url, userName, password)) {
    DSLContext cxt = DSL.using(conn, SQLDialect.POSTGRES);
    TesttabRecord r = new TesttabRecord(1, "test");
    Testfunc f = new Testfunc();
    f.setVal(r);

    f.execute(cxt.configuration());
}

Get exception:

Jan 19, 2018 10:17:26 AM org.jooq.tools.JooqLogger warn WARNING: Not supported by dialect : Type class test.db.tables.records.TesttabRecord is not supported in dialect POSTGRES org.jooq.exception.SQLDialectNotSupportedException: Type class test.db.tables.records.TesttabRecord is not supported in dialect POSTGRES at org.jooq_3.10.3.POSTGRES.debug(Unknown Source) at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:855) at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:799) at org.jooq.impl.DefaultBinding.toSQLCast(DefaultBinding.java:358) at org.jooq.impl.DefaultBinding.sql(DefaultBinding.java:290) at org.jooq.impl.Val.accept(Val.java:80) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.Tools.renderAndBind(Tools.java:1945) at org.jooq.impl.SQLImpl.accept(SQLImpl.java:66) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.SQLField.accept(SQLField.java:65) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.QueryPartList.accept(QueryPartList.java:101) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.Function.toSQLArguments1(Function.java:445) at org.jooq.impl.Function.toSQLArguments0(Function.java:427) at org.jooq.impl.Function.toSQLArguments(Function.java:422) at org.jooq.impl.Function.accept(Function.java:226) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.AbstractRoutine$RoutineField.accept(AbstractRoutine.java:1635) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.Tools.renderAndBind(Tools.java:1945) at org.jooq.impl.SQLImpl.accept(SQLImpl.java:66) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.SQLTable.accept(SQLTable.java:82) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.QueryPartList.accept(QueryPartList.java:101) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.SelectQueryImpl.toSQLReference0(SelectQueryImpl.java:1190) at org.jooq.impl.SelectQueryImpl.toSQLReference0(SelectQueryImpl.java:978) at org.jooq.impl.SelectQueryImpl.toSQLReferenceLimitDefault(SelectQueryImpl.java:771) at org.jooq.impl.SelectQueryImpl.accept(SelectQueryImpl.java:604) at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:415) at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:423) at org.jooq.impl.AbstractContext.visit(AbstractContext.java:184) at org.jooq.impl.AbstractQuery.getSQL0(AbstractQuery.java:498) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:306) at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:317) at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2597) at org.jooq.impl.AbstractRoutine.executeSelectFromPOSTGRES(AbstractRoutine.java:433) at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:335) at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:317)

What's wrong with code above? I'll appreciate any suggestions.

Any chance to edit your question to make it more human readable and share a little info with it? First text, problem-statement and thereafter start posting code. — ZF007
– ZF007, Commented Jan 20, 2018 at 23:02
The error message is pretty clear, isn't it? The jooq doesn't allow to use PostgreSQL JDBC for this kind of task. — Kalle Richter
– Kalle Richter, Commented Jan 20, 2018 at 23:36
@KarlRichter I couldn't find any info about such limitations so I posted question here. — codemonkey
– codemonkey, Commented Jan 21, 2018 at 1:16

Lukas Eder · Accepted Answer · 2018-02-15 15:26:37Z

2

This is a bug / missing feature: https://github.com/jOOQ/jOOQ/issues/7174

There's currently (jOOQ 3.10) no good workaround for it, short of overloading the function and passing either:

A composite type that looks like the table type (CREATE TYPE)
Individual arguments per table column

On the syntax of your function:

There's a minor glitch in your function as well:

CREATE OR REPLACE FUNCTION public.testfunc(val testtab)
RETURNS void
LANGUAGE 'plpgsql'
AS //
BEGIN
  INSERT INTO testtab VALUES (val.*); -- You cannot use val in FROM
END;
//;

edited Feb 15, 2018 at 15:26

answered Feb 15, 2018 at 14:51

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

asm0dey · Accepted Answer · 2018-01-23 04:40:07Z

0

There shoud be Routines class generated, which should include your routine. There you will find which arguments it takes.

answered Jan 23, 2018 at 4:40

asm0dey

2,9492 gold badges23 silver badges35 bronze badges

Collectives™ on Stack Overflow

Cannot execute PostgreSQL function with Jooq

2 Answers 2

On the syntax of your function:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

On the syntax of your function:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related