How to make "SELECT count(to_regclass('clt_cmd'))" work?

[Thorsten Schöning]

Hi all,

I need to check for the availability of a table and am using a
construct found on SO:

> SELECT to_regclass('schema_name.table_name');

https://stackoverflow.com/a/24089729/2055163

Translated to Java:

> DSL.count(DSL.field
> (
> "to_regclass({0})",
> SQLDataType.CLOB,
> SUMMARY_WITH_PERIOD.NAME
> ))

That doesn't work, because double quotes are used and make Postgres
think a column instead of a relation/table is used:

> SELECT count(to_regclass("clt_cmd"))

Using "[...].NAME.unquotedName()" has the same result, one really
needs to use '...' instead of "...". Something like the following in
combination with "unquotedName()" doesn't work as well, because then
"{0}" doesn't get replaced at all.

> "to_regclass('{0}')"

It seems I really need to use String.format or similar to build the
whole function call myself:

> String.format("to_regclass('%s')",
> SUMMARY_WITH_PERIOD.NAME.unquotedName())

I already tried to add CAST to "regclass", but nothing worked, "..."
is interpreted as a column.

Am I missing something or do I really need to build the function call
using plain strings? Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hi Thorsten,

Template placeholders are not replaced inside of string literals as documented here:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/  

So this doesn't work: "to_regclass('{0}')". What you need is DSL.inline() (or DSL.val()) to produce a string literal containing your qualified column name, e.g. DSL.inline(SUMMARY_WITH_PERIOD.NAME.toString()), which you can then pass to this template: "to_regclass({0})".

I hope this helps

Lukas

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1737293635.20200421121520%40am-soft.de.

[Thorsten Schöning]