Repository navigation
LIMIT parameter type should be sql.NullInt32? #210
Description
First of all - thank you for this extremely useful tool!
Sqlc uses int32 type as parameter for LIMIT in queries, for example for this query
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name
LIMIT $1 OFFSET $2;sqlc will generate this set of params:
type ListAuthorsParams struct {
Limit int32
Offset int32
}According to Postgres docs it should be also possible to use NULL as the argument to LIMIT to effectively omit the LIMIT clause. This is very useful for queries where LIMIT is optional.
It's possible to work around this by passing some arbitrary large number as the parameter, but that seems like a hack.
Would it be possible to use something like sql.NullInt32 instead of int32 as the parameter for LIMIT so that it's possible to dynamically assign NULL to LIMIT in queries?
Activity
cmoog commentedon Dec 22, 2019
One possibility solution is to have the
Limitbe of type*int32. Then the generator would add the logic for injecting"NULL". The aws sdk takes a similar approach for nullable parameters.EnchanterIO commentedon Feb 4, 2021
On a similar note. If a table primary key is defined as BIG INT, why the offset and limit generates int32 params @cmoog ? Shouldn't that generate int64?
Is there a way to specify this in the query annotations or?
Update: COUNT(*) query generates int64 result:
EnchanterIO commentedon Feb 10, 2021
Hi @kyleconroy , do you have any idea how to resolve my above comment and make the sqlc to generate the attributes of type int64 instead of int32 please?
kyleconroy commentedon Aug 28, 2021
@EnchanterIO You can always cast the parameter to the correct type
kyleconroy commentedon Aug 28, 2021
Changing this to default to a
NullInt32is a breaking change I'm not willing to make right now. That said, #1155 will address this for all parameters, so I'm going to close this in favor of that issue.EnchanterIO commentedon Aug 29, 2021
Wou I didn't know about this casting
$1::bigintTHANKS. Will try that tomorrow. That will do for now, I understand breaking changes are only possible in long-term releases. Thanks again @kyleconroy for awesome work on SQLC.GuillaumeDesforges commentedon Nov 27, 2024
In combination with #1665, it is effectively not possible to use a nullable parameter in LIMIT:
LIMIT sqlc.narg(limit)raises a parse errorLIMIT $makes a non-nullable parameterWhat is the recommended way to go around this?
EDIT: this works:
It is important to quote in
sqlc.nargin order for the parser not to fail.