Using a function with custom types?

[tamis-laan]

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?

[orisano]

According to https://postgis.net/docs/ST_GeomFromText.html, shouldn't $3 be TEXT?

[tamis-laan]

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.

[orisano]

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

[tamis-laan]

Ah oke, then I guess I'm stuck.

[tamis-laan]

@orisano BTW from this issue I understand it should work:
#1004
Or am I missing something

[orisano]

The solution to that issue is to skip the functions that sqlc does not know.
So the type will be interface{}.

[andrewmbenton]

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.