How do I pass a nil/null parameter to a stored function?

Question

The server receives a GET request which has the territory_id parameter. This parameter can be nil if the user does not need a filter by territory, or may have an integer number if the user wants to get records only for a specific territory.

Code:

func GetDataList (response http.ResponseWriter, request * http.Request) {
    params: = GetRequestParams (request.URL.Query ())
    normalID, _: = strconv.Atoi (params ["normal_id"])
    territoryId, _: = strconv.Atoi (params ["territory_id"])

    var listToCheck [] ObjectDataNormal
    query = `select * from get_list_for_predict (year: = $1, territory_in: = $2)`
    rows, err = db.Queryx (query,
        2011,
        territoryId)
    if rows! = nil {
            // code
    }
    // code
}

func GetRequestParams (values url.Values) map [string] string {
    urlValues: = make (map [string] string, len (values))
    for k, v: = range values   {
        urlValues   [k] = v [0]
    }
    return urlValues
}

The stored procedure in postgresql is designed so that if territory_in is ISNULL, then the filter for this parameter does not work, otherwise, if territory_in is equal to 5, for example, records are taken where the value in the territory field is 5.

And how to implement this on the server side? If I put territory_id = nil in postman, I get:

ERROR: invalid input syntax for integer: "nil"

If null, then:

ERROR: invalid input syntax for integer: "null".

Also territroyID can not be equal nil. How to pass null to a function depending on user request?

In theory I can check in postages function territory_in = 0, instead of IS NULL. Since when converting a string to int(strconv.Atoi (params ["territory_id"])) territory_in it will be set to 0. But I'm wondering how to send a null parameter to a storage function in golang?

I use sqlx and pgx.

Answer 1

If you save territoryId as a *int instead of int, you can pass nil:

func strToIntPtr(s string) *int {
    i, err := strconv.Atoi(s)
    if err != nil {
        return nil
    }
    return &i 
}

territoryId := strToIntPtr(params["territory_id"])

And then territoryId will be nil anytime it's not a valid int. So they can pass nil, null, or any (or anything that's not a number) and you just treat it as null.