How to pass current record as parameter to a routine?

Question

I am attempting to invoke a PL/SQL function via jOOQ using the routines. The function is as follows:

CREATE OR REPLACE FUNCTION myschema.default_sort(rec myschema.mytable)
  RETURNS text
  LANGUAGE SQL
  IMMUTABLE
AS $$
  SELECT
      TO_CHAR($1.created_at, 'YYYYMMDDHHMMSSMS') ||  myschema.encode($1.id)
$$;

I am looking to be able to do the equivalent of the following:

SELECT 
    mt.*,
    myschema.default_sort(mt)
FROM
    myschema.mytable mt;

So in jOOQ, the routine is generated:

public static Field<String> defaultSort(MyTableRecord rec) {
//do stuff
}

But when it comes time to invoke, I do not know how to pass the current row in the the routine:

dsl.select(
        Tables.MYTABLE.asterisk(),
        Routines.defaultSort( /* What goes here? */)
    )
        .from(Tables.MYTABLE)

I can't find anything on the Tables or dsl object that suggests a current record. How can I do this?

Lukas Eder · Accepted Answer · 2021-06-28T06:36:43.663

As of jOOQ 3.15, you cannot pass a Field<TableRecord> expression to a generated stored function reference yet, only a TableRecord value, which is constructed in the client. This would definitely be an interesting improvement in a near-future version of jOOQ. jOOQ 3.15 will start allowing to use ad-hoc ROW types as SelectField for projections: https://github.com/jOOQ/jOOQ/issues/11812

It would make sense to also offer this kind of functionality to Table types, which are nominal ROW types:

In the meantime, as always when a vendor-specific feature is missing in jOOQ, you can resort to plain SQL templating and use:

DSL.field("myschema.default_sort({0})", SQLDataType.VARCHAR, MYTABLE);

How to pass current record as parameter to a routine?

1 Answers1