Request for LIMIT and OFFSET clauses
Suppose I want to select the first row of the artist
table, I'd do a query like this:
ql -db test.db 'SELECT * FROM artist LIMIT 1'
However, it seems unsupported: the docs don't mention any LIMIT
clause and the error message thrown is this:
2014/04/09 06:40:58 BEGIN TRANSACTION; SELECT * FROM artist LIMIT 1; COMMIT;
2014/04/09 06:40:58 1:41 syntax error
Adding LIMIT
would be a nice improvement for optimization, as it would allow the user to reduce the number of results given, or to pick just one result at will.
OFFSET
would be another nice addition. I'd use OFFSET
to skip n results before starting to count my result limit, for example, if I want only the second result of this query:
ql -db test.db 'SELECT * FROM artist ORDER BY name DESC OFFSET 1 LIMIT 1'
OFFSET
and LIMIT
could also be used to display paginated results (50 total results can be shown as 10 results per page over 5 pages).
For IDs, ql
uses an id()
function, maybe a rownum()
function could be added? this way we could implement OFFSET
and LIMIT
manually
ql -db test.db 'SELECT * FROM artist WHERE rownum() >= 1 AND rownum() <= 2 ORDER BY name DESC'
However, OFFSET
and LIMIT
would be more natural than rownum()
magic to PostgreSQL users.