jOOQ - execute postgres user defined function with refcursor

Question

I'm having some problems with executing functions that takes and returns postgres refcursor in jooq. I have no idea how to approach with instantiation the ref, which is Result<Record> and how to loop through the records I should get from the function I want to execute.

Let's say I have a following function in postgres (postgres 9.5):

create or replace function foo_cursor(name character varying, ref refcursor)
returns refcursor
as $func$
begin
  open ref for
    select id, first_name, last_name
    from students
    where first_name = name;
  return ref;
end
$func$ 
language plpgsql;

And in postgres I'm executing in like that:

begin;
select foo_cursor('Konrad', 'my_cursor');
fetch all in "my_cursor";
commit;

The function has to stay the same - it returns refcursor and takes refcursor.

And I want to execute it in jOOQ:

Routines.fooCursor(configuration, "Konrad", ____);

But I don't know what to put inside the ____, which takes Result<Record>. I tried something like:

Result<Record> records = DSL.using(configuration).newResult();

but it also didn't work.

Lukas Eder · Accepted Answer · 2018-06-06 12:02:41Z

1

jOOQ supports the refcursor result type (or OUT parameter) in PostgreSQL, but not the IN parameter type, which is really a bit of a quirk, given that it pretends to be a pseudo type for an identifier. If you can overload your function to this:

create or replace function foo_cursor(name character varying)
returns refcursor
as $func$
declare
  ref refcursor;
begin
  open ref for
    select id, first_name, last_name
    from students
    where first_name = name;
  return ref;
end
$func$ 
language plpgsql;

Then, jOOQ will be able to call the function. There's no need for jOOQ (or you, when using jOOQ) to define the resulting cursor name.

You may, however, need to run the function call inside of a transaction.

edited Jun 6, 2018 at 12:02

answered Jun 6, 2018 at 10:52

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

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

4 Comments

kodi1911 Over a year ago

Thank you for your reply. I checked that, but when I use Routines.fooCursor(configuration) it gives me DataAccessException with message org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist. I tried to instantiate the refcursor with declare ref refcursor := 'mycursor'; but then I would get the same error that tells me the mycursor does not exist.

Lukas Eder Over a year ago

@kodi1911: Are you running your jOOQ code in a transaction?

kodi1911 Over a year ago

running it inside context.transactionResult(config -> Routines.fooCursor(config).getValues("first_name", String.class)); does the work. Thank you for your help. You're great :)

Lukas Eder Over a year ago

Indeed, I vaguely remembered transactions being a requirement when calling refcursor functions from JDBC clients... Thanks for the nice words :)

Collectives™ on Stack Overflow

jOOQ - execute postgres user defined function with refcursor

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related