Call postgres function with array param in jooq

Question

How can I pass array param in postgreSQL function in native SQL with jooq?

The name of function compiled dynamically and I want to call it in string:

dsl().execute(someFunction + "(?)", Arrays.asList(1, 2, 3));

Functions take argument with type integer[]

I've found ugly solution:

dsl().execute(someFunction + "(string_to_array(?, ',')::INT[])", Joiner.on(",").join(1, 2, 3));

I am using jooq version 3.6.4

Lukas Eder · Accepted Answer · 2016-09-17 14:28:49Z

2

Don't use string concatenation

Both of your attempts use string concatenation to produce the function call: someFunction + "(?)". While you're probably in full control of these strings, there's always a slight risk of adding unnecessary:

Syntax errors
SQL injection vulnerabilities

Better use jOOQ's built-in templating mechanism:

dsl().execute("{0}({1})", DSL.name("someFunction"), bindValue);

The templating mechanism is documented here: http://www.jooq.org/doc/latest/manual/sql-building/queryparts/plain-sql-queryparts

Pass array bind variables

It's easy. Just pass the bind variable as an array (not a list) in Java. For instance:

dsl().execute("someFunction({0})", DSL.val(new Integer[] { 1, 2, 3 }));

answered Sep 17, 2016 at 14:28

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Artem Over a year ago

Thanks for the answer! It is a better way. Of cause, I use only array of integers and function's names in code, that do not promote SQL injection.

Collectives™ on Stack Overflow

Call postgres function with array param in jooq

1 Answer 1

Don't use string concatenation

Pass array bind variables

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Don't use string concatenation

Pass array bind variables

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related