Parameterising `LIMIT ALL` in pg-promise

I have this simple query:

SELECT * FROM table ORDER BY ${pageSortKey:name} ${pageSortDir:raw} LIMIT ${pageLimit} OFFSET ${pageOffset}

How do I pass ALL for LIMIT ?

As far as I understand it - ${pageLimit} is treated as an int and thus if I try to pass the argument 'ALL' to it, I got:

"invalid input syntax for type bigint: \"ALL\""

Much appreciated for reading.

Solution

The simplest is to make it use the value directly, by changing the variable to ${pageLimit:raw}.

A safer approach is to create your own custom type for it, using Custom Type Formatting:

const limit = val => ({rawType: true, toPostgres: () => val ?? 'ALL'});

Then pass the function result as the value into the query, which will produce:

limit(0) => 0
limit(123) => 123
limit() => ALL
limit(null) => ALL

The second approach is better, but usually, you would choose to wrap the entire LIMIT logic into it, and not just the limit value:

const limit = val => ({ rawType: true, toPostgres: () => val ? `LIMIT ${val}` : '' });

To produce the entire LIMIT clause only when there is an actual limit to be applied:

limit(0) => ``
limit() => ``
limit(null) => ``
limit(123) => LIMIT 123

examples:

db.any('SELECT * FROM table $1', [limit(123)]);
//=> SELECT * FROM table LIMIT 123

db.any('SELECT * FROM table ${limit}', {limit: limit(123)});
//=> SELECT * FROM table LIMIT 123