CODEWITHSUNDEEP
javasqlpostgresqljooq
codemonkey
codemonkey

Reputation: 157

Cannot execute PostgreSQL function with Jooq

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.

Upvotes: 1

Views: 1251

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220762

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

Upvotes: 2

asm0dey
asm0dey

Reputation: 2931

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

Upvotes: 0

Related Questions