-
Notifications
You must be signed in to change notification settings - Fork 923
Using a function with custom types? #2603
-
using the following sql:
-- name: CreateReport :one INSERT INTO reports (title, description, location) VALUES ($1, $2, ST_GEOMFROMTEXT($3)) RETURNING id, title, description, location;
I get the following generated struct:
type CreateReportParams struct { Title string `json:"title"` Description string `json:"description"` StGeomfromtext interface{} `json:"st_geomfromtext"` }
As you can see for some reason the ST_GEOMFROMTEXT function is interpreted as a variable. The correct output should be:
type CreateReportParams struct {
Title string `json:"title"`
Description string `json:"description"`
Location types.PostGISPoint `json:"location"`
}Why does this happen? How can I deal with this?
Beta Was this translation helpful? Give feedback.
All reactions
As you can see for some reason the ST_GEOMFROMTEXT function is interpreted as a variable.
Not exactly. It's a bit confusing, but sqlc has simply picked a name for the parameter struct field based on the function name. If you aren't happy with the name, you can use sqlc.arg() to change it like this: (1,ドル 2,ドル ST_GEOMFROMTEXT(sqlc.arg(location))).
Why does this happen? How can I deal with this?
I will assume by "this" you mean "the struct field for the location query parameter has type interface{} rather than types.PostGISPoint" since I've already addressed the naming issue above.
@orisano provides a good explanation above of the type inference issues involved. The ST_GEOMFROMTEXT functi...
Replies: 3 comments 7 replies
-
According to https://postgis.net/docs/ST_GeomFromText.html, shouldn't 3ドル be TEXT?
Beta Was this translation helpful? Give feedback.
All reactions
-
Yes so the Value function would produce the TEXT SRID=4326;POINT(%.8f %.8f) which would result in:
ST_GEOMFROMTEXT(SRID=4326;POINT(%.8f %.8f))
Or am I missing something?
If I correct the generated code as described above it works properly.
Beta Was this translation helpful? Give feedback.
All reactions
-
You are using overrides in your configuration to set geometry to types.PostGISPoint, right?
The type of the first argument of ST_GeomFromText is text, not geometry, so override will not work correctly.
and sqlc does not currently support postgis. So it is not possible to know the type of ST_GeomFromText properly.
Therefore, the type is interface{}.
https://github.com/sqlc-dev/sqlc/blob/867b8e0ea2e691336dee0a92d7a5f6205193188d/internal/engine/postgresql/extension.go
Beta Was this translation helpful? Give feedback.
All reactions
-
Ah oke, then I guess I'm stuck.
Beta Was this translation helpful? Give feedback.
All reactions
-
Beta Was this translation helpful? Give feedback.
All reactions
-
The solution to that issue is to skip the functions that sqlc does not know.
So the type will be interface{}.
Beta Was this translation helpful? Give feedback.
All reactions
-
As you can see for some reason the ST_GEOMFROMTEXT function is interpreted as a variable.
Not exactly. It's a bit confusing, but sqlc has simply picked a name for the parameter struct field based on the function name. If you aren't happy with the name, you can use sqlc.arg() to change it like this: (1,ドル 2,ドル ST_GEOMFROMTEXT(sqlc.arg(location))).
Why does this happen? How can I deal with this?
I will assume by "this" you mean "the struct field for the location query parameter has type interface{} rather than types.PostGISPoint" since I've already addressed the naming issue above.
@orisano provides a good explanation above of the type inference issues involved. The ST_GEOMFROMTEXT function expects a parameter of type text so it would be fair to expect sqlc to figure out that the location parameter struct field type should be string rather than interface{}. Unfortunately sqlc doesn't know anything about postgis functions so this expectation fails. And expecting sqlc to use types.PostGISPoint for that parameter doesn't make sense.
But it may satisfy your needs to simply use an explicit cast to get sqlc to do the "right thing" in this case. I haven't tested this, but if you alter the VALUES clause to be something like (1,ドル 2,ドル ST_GEOMFROMTEXT(sqlc.arg(location)::text)) then you'll get a struct field named Location that's of type string and you can put your fmt.Sprintf("SRID=4326;POINT(%.8f %.8f)", p.Lon, p.Lat) value in there.
Beta Was this translation helpful? Give feedback.
All reactions
-
@tamis-laan have you managed to fix this? If I use fmt.Sprintf("SRID=4326;POINT(%.8f %.8f)", X , Y) I still get interface {} is string, not []uint8
Beta Was this translation helpful? Give feedback.