Golang with postgres - unable to pass variable into a ST_DWithin query

Question

Very new to Golang and unable to resolve this. Any help would be highly appreciated.

This is the code I need help with. Trying to pass the latitude etc into the query below, but unable to do it. Looking for the equivalent on ${latitude} in node js...

   latitude := r.URL.Query().Get("lat")
   longitude := r.URL.Query().Get("lon")
   radius := r.URL.Query().Get("rad")


   row := db.QueryRow(`SELECT "name", "website", "coordinates", 
   "description", "rating"
    FROM geospots
    WHERE ST_DWithin("coordinates", ST_MakePoint(latitude = $1, 
   longitude = $2)::geography, radius = $3)`, latitude, longitude)


   Error: 
   andlers/SpotsInArea.go:15:5: latitude declared but not used
   handlers/SpotsInArea.go:16:5: longitude declared but not used
   handlers/SpotsInArea.go:17:5: radius declared but not used

Go doesn't have native string interpolation. And certainly not with builtin sql-injection protection. If you want to pass the variables as arguments to the query pass them as arguments to the QueryRow method just after the SQL string. And also you need to put parameter placeholders into the SQL string. For postgres it's $1, $2, and so on. — mkopriva
– mkopriva, Commented Mar 2, 2022 at 15:55
As an example take a look at this. You can see that there the SQL string uses $1 once and $2 twice and then passes two arguments "orange" and 64. The $1 in the SQL will be replaced with "orange" and the two $2 will both be replaced with 64. — mkopriva
– mkopriva, Commented Mar 2, 2022 at 15:58
mkopriva Thanks very much for taking the time to reply. Based on that, I would have thought that the following code would have worked, but it doesn't. It's looking for columns with these titles. It also doesn't accept any hardcoded values there. Can you see what I am doing wrong? row := db.QueryRow(SELECT "name", "website", "coordinates", "description", "rating" FROM geospots WHERE ST_DWithin("coordinates", ST_MakePoint(latitude = $1, longitude = $2)::geography, radius = $3), latitude, longitude) — Nir Shihor
– Nir Shihor, Commented Mar 2, 2022 at 16:18
Update the question with the new code. Also, include the exact error message that you are getting. — mkopriva
– mkopriva, Commented Mar 2, 2022 at 16:19
It should be just $1 instead of latitude = $1. Same for the other three parameters. And you MUST pass all three arguments not just lat and long, i.e. where is radius? And the error means that lat long and radius are declared in a scope where they are not used. That means that the QueryRow that you have show is executed in some other function which has no access to those three variables. In Go v := x does not declare global variables if that's what you were hoping for. — mkopriva
– mkopriva, Commented Mar 2, 2022 at 16:25

Chandan · Accepted Answer · 2022-03-02 17:26:03Z

3

Thanks to @mkopirva

As pointed you don't need latitude = $1 it should be just $1, You have defined $3 in query but missing the 3rd value

And make sure the variable are within the scope where you are accessing them

row := db.QueryRow(`
SELECT
    "name",
    "website",
    "coordinates",
    "description",
    "rating"
FROM
    geospots
WHERE
    ST_DWithin(
        "coordinates",
        ST_MakePoint($1, $2)::geography,
        $3
    )`,
    longitude, latitude, radius
)

edited Mar 2, 2022 at 17:26

answered Mar 2, 2022 at 17:12

Chandan

11.8k1 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Golang with postgres - unable to pass variable into a ST_DWithin query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related