I am calling a POSTGRES PLSQL function from r2dbc, that returns a SETOF foo_type, where foo_type is a user-defined type. Basically this works, but the call returns rows with only a single String column, that is CSV-like formatted, e.g. "(bar,1234)".

Is there a way to return a composite type that can be treated like table columns from a normal SELECT?

My SQL code:

CREATE TYPE foo_type AS (
    info1 VARCHAR,
    info2 BIGINT
);

CREATE OR REPLACE FUNCTION foo(input VARCHAR)
    RETURNS SETOF foo_type AS $$
DECLARE
    entry RECORD;
BEGIN
    entry := ROW(input, CAST(1234 AS BIGINT));
    RETURN NEXT entry;
END;
$$ LANGUAGE plpgsql;

My Java code, that I’d like to use:

public Flux<String> foo(String input) {
    return databaseClient
        .sql("SELECT foo(:input)")
        .bind("input", input)
        .map((row, rowMetaData) -> process(row.get(0, String.class), row.get(1, Long.class))
        .all();
}

This does not work. I can use row.get(0, String.class) and perform String.split on the commas, but this seems odd. My real code is of course a bit more complex:

• more attributes in the TYPE
• the returned rows are build with different means: SELECTs on TABLEs and manual ROW creations like above.