CODEWITHSUNDEEP
postgresqljooq
Juri Krainjukov
Juri Krainjukov

Reputation: 742

Getting results from table-valued Postgresql function with JOOQ

I have a Postgresql function that returns Table 

CREATE OR REPLACE FUNCTION test_func(IN param1 text, IN param2 integer)
  RETURNS TABLE(result1 text, result2 integer) AS
  $BODY$
  BEGIN
   result1 := 'aa';
   result2 :=1;
   return next;
   result1 := 'bb';
   result2 :=2;
   return next;
  END
  $BODY$
  LANGUAGE plpgsql

Query in pg-admin returns correct result 

select * from test_func('aaa', 23);
result1 | result2
"aa"    | 1
"bb"    | 2

JOOQ generates function as always

...
public class TestFunc extends org.jooq.impl.AbstractRoutine<java.lang.Void> {
...
public TestFunc() {
    super("test_func", ee.tavex.tavexwise.db.public_.Public.PUBLIC);

    addInParameter(PARAM1);
    addInParameter(PARAM2);
    addOutParameter(RESULT1);
    addOutParameter(RESULT2);
}
...

and in Routines class

...
public static ee.tavex.tavexwise.db.public_.routines.TestFunc testFunc(org.jooq.Configuration configuration, java.lang.String param1, java.lang.Integer param2) {
    ee.tavex.tavexwise.db.public_.routines.TestFunc p = new ee.tavex.tavexwise.db.public_.routines.TestFunc();
    p.setParam1(param1);
    p.setParam2(param2);

    p.execute(configuration);
    return p;
}

I call it this way

TestFunc records = Routines.testFunc(dslConfiguration, "xx", 10);


records.getResults()  //returns empty List
records.getResult1() //returns "aa"
records.getResult2() //returns 1

So, it correctly returns the first row's values, but how can I get the whole table?

(jooq 3.5.0)

Upvotes: 1

Views: 988

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

The correct way to call table-valued functions from jOOQ is by using them in FROM clauses as documented in the manual page that you've linked.

In your case, that would be:

Result<TestFuncRecord> result =
DSL.using(configuration)
   .selectFrom(Routines.testFunc("xx", 10))
   .fetch();

Or starting with jOOQ 3.6 also

Result<TestFuncRecord> result =
DSL.using(configuration)
   .selectFrom(Tables.TEST_FUNC("xx", 10))
   .fetch();

The jOOQ code generator treats table-valued functions like ordinary tables, not like routines. This is why there should be no method in Routines that takes a Configuration argument.

Upvotes: 2

Related Questions