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.