CODEWITHSUNDEEP
javapostgresqlstored-proceduresjooq
Artem
Artem

Reputation: 505

Stored procedures in jooq with dynamic names

I want to call stored procedures from PostgreSQL in JOOQ by name dynamically:

final Field function = function("report_" + name, Object.class, (Field[])params.toArray(new Field[params.size()]));

dsl().select(function).fetchArrays();

For example it generates:

select report_total_requests('83.84.85.3184');

Which returns:

 report_total_requests 
-----------------------
 (3683,2111,0)
 (29303,10644,1)

And in java it is array of "(3683,2111,0)" objects.

I want to generate:

select * from report_total_requests('83.84.85.3184')

To produce:

total | users | priority 
------+-------+----------
 3683 |  2111 |        0
29303 | 10644 |        1

That is in java array of arrays of objects

Any ideas?

Upvotes: 2

Views: 560

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220942

The way forward is to use plain SQL as follows:

Name name = DSL.name("report_" + name);
QueryPart arguments = DSL.list(params);
dsl().select().from("{0}({1})", name, arguments).fetch();

Note, I've wrapped the function name in a DSL.name() object, to prevent SQL injection.

Upvotes: 2

Related Questions