How to put function to select?

[kobalicek]

Hi,

How can I use function in select. For example I would like to do something like this:
...
knex.column("to_char(dateCreated) as dateCreated");
...
without having to write the whole select as a raw select.

Thanks

[johanneslumpe]

You can just pass in a knew.raw statement, like this:

knex.column(knex.raw('to_char(dateCreated) as dateCreated'));

[kobalicek]

Hi, thanks!

I found this solution myself, although it took me some time before I tried to use raw here.

Anyway I think that it would be nice to have direct support for having functions in select, so knex can autoescape field/table names there.

I think that this can be closed, thanks!

[bendrucker]

@kobalicekp Certainly something to consider. But it is worth noting that knex doesn't support arbitrary SQL functions anywhere. It's either a supported command or you use knex.raw. Out of curiosity, why are you worried about escaping? Are you using this somewhere else where there's an injection vulnerability?

[kobalicek]

@bendrucker I meant escaping just to make the queries more portable. My query from the example above would look like:

knex.column(knex.raw('to_char("dateCreated", \'YYYY-MM-DD\') as "dateCreated"'));

I'm not sure how portable it would be outside of postgres. But if I think about it it would be difficult to write a good wrapper for this, covering all possible functions and their arguments.

[bendrucker]

You hit on the exact problem. RDBMSes have 100s of functions and even where there's overlap the syntaxes are usually different. But by all means you should monkey patch functions you need often to keep things clean.

[eduardobcastro]

It would be good to have wrappers to the most common functions like COALESCE / NVL, UPPER, LOWER, etc, and also CASE/WHEN/ELSE

[elhigu]

@eduardobcastro please do those if you wish. knex.fn.* is place to implement them and remember to add also tests + docs. And remember to test also that they works in a nested manner.

To be honest I don't see much use to have them as own functions, since many times one still needs to add casting etc. to when used and using knex not that complicated to use, unless there are subtle differences between how those functions work between db dialects .