INSERT...RETURNING with output_expression

Question

PostgresQL INSERTs allow returning an output_expression using any subset of the inserted columns, but I cannot get the syntax right for using this feature with jOOQ.

This is the PL/pgSQL equivalent of what I want to achieve:

CREATE TABLE my_table (i INTEGER, t TEXT);

DO 
    $$DECLARE my_var TEXT; 
BEGIN 
    INSERT INTO my_table(i, t) 
        VALUES(1, 'hello') 
        RETURNING (t) INTO my_var;

    RAISE NOTICE 'Inserted text was --> % <--', my_var; 
END$$;

But the problem I find is this syntax is invalid in jOOQ:

String retVal = using(configuration)
    .insertInto(MY_TABLE,
        MY_TABLE.I,
        MY_TABLE.T)
    .values(i, t)
    .returning(MY_TABLE.T)
    .fetchInto(String.class);

The closest I've been is using fetchOne to get a full MyTableRecord and picking the fields I want. Is there another way telling jOOQ to fetch exactly the type of variables I want?

P.D.: I'm using jOOQ 3.9.2

Answer 1

The workaround I have found is the following, it takes advantage of Java's Optional:

String retVal = using(configuration)
    .insertInto(MY_TABLE,
        MY_TABLE.I,
        MY_TABLE.T)
    .values(i, t)
    .returning()
    .fetchOptional()
    .map(MyTableRecord::getT)
    .orElse(null);

Answer 2

You can use .fetch().into(String.class)