CODEWITHSUNDEEP
stored-proceduresjava-8jooqpostgresql-9.4
invis
invis

Reputation: 1100

jooq generates not compiled code from Postgres procedures

Now I am trying to use JOOQ in new version of program that communicate with 2 DBs simultaneously. But problem is coming from Postgres procedures which I cant deactivate cause of Routine generation cannot be deactivated, even if I don't need them in my program.

Problem 1

Jooq understand some procedures like a tables. I am not pro with SQL so I don't know why this happens, maybe cause of procedure return type? Code for generate one of procedures that have this "bug":

CREATE OR REPLACE FUNCTION dblink_get_notify(IN conname text, OUT notify_name text, OUT be_pid integer, OUT extra text)
  RETURNS SETOF record AS
'$libdir/dblink', 'dblink_get_notify'
  LANGUAGE c VOLATILE STRICT
  COST 1
  ROWS 1000;
ALTER FUNCTION dblink_get_notify(text)
  OWNER TO postgres;

Maybe cause of problem is fact that there is another procedure with the same name, but without IN parameter:

    CREATE OR REPLACE FUNCTION dblink_get_notify(OUT notify_name text, OUT be_pid integer, OUT extra text)
  RETURNS SETOF record AS
'$libdir/dblink', 'dblink_get_notify'
  LANGUAGE c VOLATILE STRICT
  COST 1
  ROWS 1000;
ALTER FUNCTION dblink_get_notify()
  OWNER TO postgres;

Problem 2

Some generated classes from procedures have compile errors (procedure above have this bug too enter image description here)

I will give you another example:

    CREATE OR REPLACE FUNCTION bt_page_stats(IN relname text, IN blkno integer, OUT blkno integer, OUT type "char", OUT live_items integer,
OUT dead_items integer, OUT avg_item_size integer, OUT page_size integer, OUT free_size integer, OUT btpo_prev integer,
OUT btpo_next integer, OUT btpo integer, OUT btpo_flags integer)
  RETURNS record AS '$libdir/pageinspect', 'bt_page_stats'
  LANGUAGE c VOLATILE STRICT
  COST 1;
ALTER FUNCTION bt_page_stats(text, integer)
  OWNER TO postgres;

JOOQ understands this procedure as a routine. But generated code have identical twice Parameter<Integer> BLKNO field. And what I found strange is constructor of that class:

/**
 * Create a new routine call instance
 */
public BtPageStats() {
    super("bt_page_stats", Public.PUBLIC);

    addInParameter(RELNAME);
    addInOutParameter(BLKNO);
    addInOutParameter(BLKNO);
    addOutParameter(TYPE);
    addOutParameter(LIVE_ITEMS);
    addOutParameter(DEAD_ITEMS);
    addOutParameter(AVG_ITEM_SIZE);
    addOutParameter(PAGE_SIZE);
    addOutParameter(FREE_SIZE);
    addOutParameter(BTPO_PREV);
    addOutParameter(BTPO_NEXT);
    addOutParameter(BTPO);
    addOutParameter(BTPO_FLAGS);
}

Look at double addOutParameter(BLKNO)!

Wooh, think thats all. Hope you can help me with that problems :)

Upvotes: 2

Views: 562

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

You ran into bug #4055. As of jOOQ 3.6, overloaded table-valued functions generate code that doesn't compile.

But problem is coming from Postgres procedures which I cant deactivate cause of Routine generation cannot be deactivated, even if I don't need them in my program.

That's true, but you can exclude them from the code generator explicitly by name, e.g. by specifying:

<excludes>dblink_get_notify|bt_page_stats</excludes>

More info about the code generator configuration can be found here

Upvotes: 4

Related Questions