Getting results from table-valued Postgresql function with JOOQ

Question

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)

No. I'm trying to make the request using JOOQ renerated routines. Now the one generated is identical to the one I get, when function returns only one Record. I can't get result described here jooq.org/doc/3.5/manual/sql-building/table-expressions/… — Juri Krainjukov
– Juri Krainjukov, Commented Feb 8, 2015 at 14:23
@user693442: Can you show the jOOQ code that you're using? Can you also show the function body, just to be sure there's nothing wrong in there? — Lukas Eder
– Lukas Eder, Commented Feb 8, 2015 at 16:24
Thanks for the update. Interesting, I don't get that particular testFunc(Configuration, String, Integer) method (see my answer). There might be a subtle bug. Would you mind taking this over to the jOOQ User Group? It will be easier to discuss further steps to see what might have caused the bug (perhaps the PostgreSQL version?) — Lukas Eder
– Lukas Eder, Commented Feb 9, 2015 at 8:20

Lukas Eder · Accepted Answer · 2015-02-09 08:18:29Z

2

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.

answered Feb 9, 2015 at 8:18

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

3 Comments

Lukas Eder Over a year ago

@user693442 thanks for accepting. What was the actual problem you had? I.e. did you find out what produced the "wrong" generated routines?

Juri Krainjukov Over a year ago

I had several jooq jar's of different version in my classpath. And second problem is with overloaded table-valued procedures. I posted a separate question for this stackoverflow.com/questions/28434559/…

Lukas Eder Over a year ago

@user693442: Hmm, yes that could certainly cause issues... Thanks for the feedback

Collectives™ on Stack Overflow

Getting results from table-valued Postgresql function with JOOQ

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related