Questions about calling custom postgresql function

[Jacob G]

I have some questions about calling a custom postgresql function:

1) I tried using DSL.field to call this postgresql sql function that return VOID, as follows:

private static Field<Void> foo(String param) {
   return DSL.field("ext.foo({0}, {1}, {2})", Void.class, DSL.val(param));
}

But I get this exception from creating the field:

org.jooq.exception.SQLDialectNotSupportedException: Type class java.lang.Void is not supported in dialect DEFAULT
 at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:803)
 at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:747)
 at org.jooq.impl.DSL.getDataType(DSL.java:16970)
 at org.jooq.impl.DSL.field(DSL.java:7626)

What am I doing wrong?

2) Would DSL.function be more appropriate to use instead of DSL.field in question # 1? I wasn't sure how to pass in my argument using DSL.function.

3) In question #1, is there a way to bind the schema name instead of hardcoding it in the sql? Or is putting it in the sql the best/only way?

Thank you!

[Jacob G]

I think I figured out question # 1. This works:

public static final SQLDialect SQL_DIALECT = SQLDialect.POSTGRES_9_5;

private static final DefaultDataType<Void> VOID = new DefaultDataType<>(
      SQL_DIALECT, Void.class, "VOID");

private static Field<Void> foo(String param) {

   return DSL.field("ext.foo({0}, {1}, {2})", VOID, DSL.val(param));
}

[Lukas Eder]

Hi Jacob,

Thanks very much for your enquiry. Very interesting question. I will comment inline

2016-11-08 14:56 GMT+01:00 Jacob G <jaco...@gmail.com>:

I have some questions about calling a custom postgresql function:

1) I tried using DSL.field to call this postgresql sql function that return VOID, as follows:

private static Field<Void> foo(String param) {
   return DSL.field("ext.foo({0}, {1}, {2})", Void.class, DSL.val(param));
}

But I get this exception from creating the field:

org.jooq.exception.SQLDialectNotSupportedException: Type class java.lang.Void is not supported in dialect DEFAULT
 at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:803)
 at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:747)
 at org.jooq.impl.DSL.getDataType(DSL.java:16970)
 at org.jooq.impl.DSL.field(DSL.java:7626)

What am I doing wrong?

That's just not supported (yet). I have created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/5655

You could make that a Field<Object>, which wouldn't be too wrong, because in Java, Void is a subtype of Object. The solution that you've found works as well, but I wonder if there might be a side effect.

 

2) Would DSL.function be more appropriate to use instead of DSL.field in question # 1? I wasn't sure how to pass in my argument using DSL.function.

That would work as well. You used the plain SQL API, which is much more powerful.

 

3) In question #1, is there a way to bind the schema name instead of hardcoding it in the sql? Or is putting it in the sql the best/only way?

The "best" way would be this:

DSL.field("{0}.foo({1}, {2}, {3})", schema(name("ext")), val(...), val(...), val(...));

Or, alternatively:

DSL.field("{0}({1}, {2}, {3})", name("ext", "foo"), val(...), val(...), val(...));

I.e. you create an actual org.jooq.Schema reference and put that before the dot, or you create a qualified org.jooq.Name and put that before the brackets.

Hope this helps,

Lukas

[Jacob G]

That's great, thanks so much again Lukas! You're always so helpful!

[Lukas Eder]

You're welcome, Jacob :)

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.