Add support for CAST for a column

[nt-gt]

I have a case where I want to cast a column to a VARCHAR for the comparison to support a UI feature of doing substring searches. Simplified to something like SELECT * FROM customer WHERE CAST(customerId AS VARCHAR) LIKE '%123%'.

As far as I can tell, the most portable way of doing this is to use CAST(x AS type) and so I thought I would abuse the following:

                Column x = ...;
                Column aliased = x.as(SqlIdentifier.unquoted("VARCHAR"));
                return SimpleFunction.create("CAST", Collections.singletonList(aliased));

Unfortunately, under (at least postgres) this generates the following invalid SQL:

CAST(x.VARCHAR)

Admittedly, this is abuse as column.as says it is for a different purpose. But I hope you can clarify how I would do a SQL type cast.

[schauder]

SimpleFunction doesn't do the trick here because it assumes something with comma separated arguments.

SQL.just should do the trick though.

[nt-gt]

SimpleFunction doesn't do the trick here because it assumes something with comma separated arguments.

In the given case, it only receives a single argument, which is why I thought I might pull it off in my work around. :)

SQL.just should do the trick though.

I cannot find the SQL.just (looking at org.springframework.data.relational.core.sql.SQL.just).

If you are thinking of the method where I can throw a raw string into it that will be passed "as-is", then my concern would be that I would lose the dialect mapping support (how names are formatted etc.). I have been trying hard to avoid ending up generating my own queries to ensure portability. :)

(Context: The code base I am working on used to generate the SQL itself and we had a lot of issues with corner cases because of this.)

[schauder]

The method I tried to reference is Expressions.just sorry for that.

I understand your concerns. It was intended as a workaround not a as a proper solution.

[nt-gt]

Ok. Thanks for the suggestion. :)

Also many thanks for fixing the join related issues I reported. It was very much appreciated. :)

[mp911de]

How about adding us some convenience support here?

[nt-gt]

Hi @mp911de

Assuming the comment was for me, could you elaborate a bit on what that convenience support would look like? :)

[mp911de]

We have utility classes such as Functions defining a few common functions such as count or upper. We could add CAST there as well.

[nt-gt]

Ah. Yes, a Functions.cast(column, "VARCHAR") would work quite well for me. :)