-
Notifications
You must be signed in to change notification settings - Fork 923
Trouble using function in select and insert #384
-
I have a table created like this:
CREATE TABLE subscriptions ( id UUID PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, geometry GEOMETRY(GEOMETRY, 4326) NOT NULL, start_time TIMESTAMP WITH TIME ZONE NOT NULL, end_time TIMESTAMP WITH TIME ZONE NOT NULL, owner TEXT NOT NULL, created TIMESTAMP WITH TIME ZONE NOT NULL );
To handle the geometry column, I have an override in my sqlc.yaml like this:
overrides: - go_type: "*example.com/pkg/geo.Geometry" db_type: "geometry"
The *example.com/pkg/geo.Geometry type implements sql.Scanner and driver.Valuer, but when selecting a geometry, I need to call the ST_AsBinary function first. I've written a select query like this:
-- name: GetSubscription :one SELECT id, title, description, ST_AsBinary(geometry) as geometry, start_time, end_time, owner, created FROM subscriptions WHERE id = $1 LIMIT 1;
I was hoping this would make use of the already generated Subscription type (which is the behavior with select * from subscriptions where id = 1ドル limit 1), but it creates a new type GetSubscriptionRow. Having these types is not that big a hassle, but if I have more queries that select subscriptions (e.g. by time, by area, etc.), it looks like I'll be getting a new type for each. I'm wondering if there is a way to use the ST_AsBinary function in a select without generating an additional type for the returned record.
A related problem comes up for insert/update. My insert query looks something like this:
-- name: CreateSubscription :one INSERT INTO subscriptions ( id, title, description, geometry, start_time, end_time, owner, created ) VALUES ( $1, $2, $3, ST_SetSRID(ST_GeomFromWKB($4), 4326), $5, $6, $7, $8 ) RETURNING id, title, description, ST_AsBinary(geometry) as geometry, start_time, end_time, owner, created;
This generates types like this:
type CreateSubscriptionParams struct { ID uuid.UUID `json:"id"` Title string `json:"title"` Description string `json:"description"` StGeomfromwkb interface{} `json:"st_geomfromwkb"` StartTime time.Time `json:"start_time"` EndTime time.Time `json:"end_time"` Owner string `json:"owner"` Created time.Time `json:"created"` } type CreateSubscriptionRow struct { ID uuid.UUID `json:"id"` Title string `json:"title"` Description string `json:"description"` Geometry interface{} `json:"geometry"` StartTime time.Time `json:"start_time"` EndTime time.Time `json:"end_time"` Owner string `json:"owner"` Created time.Time `json:"created"` }
Ideally, StGeomfromwkb would be named Geometry, the type would be *geo.Geometry instead of interface{}, and the tag would be json:"geometry".
And instead of the new CreateSubscriptionRow type, I could use the existing Subscription type (already in models.go). With the Geometry field being interface{}, my scanner is not getting called for the returned row.
I recognize these should probably be separate issues, but I'm wondering if it would be reasonable to support additional override properties to handle this extra SQL on encode and decode. For example, I'm imagining that I might be able configure an override like this:
overrides: - go_type: "*example.com/pkg/geo.Geometry" db_type: "geometry" encode: "ST_AsBinary(?)" decode: "ST_SetSRID(ST_GeomFromWKB(?), 4326)"
Then my queries would look like this:
-- name: GetSubscription :one SELECT * FROM subscriptions WHERE id = $1 LIMIT 1; -- name: CreateSubscription :one INSERT INTO subscriptions ( id, title, description, geometry, start_time, end_time, owner, created ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8 ) RETURNING *;
And the generated SQL would look like this:
const getSubscription = `-- name: GetSubscription :one SELECT id, title, description, ST_AsBinary(geometry) as geometry, start_time, end_time, owner, created FROM subscriptions WHERE id = 1ドル LIMIT 1 ` const createSubscription = `-- name: CreateSubscription :one INSERT INTO subscriptions ( id, title, description, geometry, start_time, end_time, owner, created ) VALUES ( 1,ドル 2,ドル 3,ドル ST_SetSRID(ST_GeomFromWKB(4ドル), 4326), 5,ドル 6,ドル 7,ドル 8ドル ) RETURNING id, title, description, ST_AsBinary(geometry) as geometry, start_time, end_time, owner, created `
Basically, I'd like to use the magic of select * from ... and ... returning * and avoid repeating column names and having to use the additional encoding/decoding functions everywhere.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1
Replies: 2 comments
-
I believe #112 would solve this problem without having to extend the configuration file. Your queries would look something like this:
-- name: Subscription :template SELECT id, title, description, ST_AsBinary(geometry)::geometry as geometry, start_time, end_time, owner, created FROM subscriptions; -- name: GetSubscription :one SELECT sqlc.tmpl('Subscription') FROM subscriptions WHERE id = $1 LIMIT 1; -- name: CreateSubscription :one INSERT INTO subscriptions ( id, title, description, geometry, start_time, end_time, owner, created ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8 ) RETURNING sqlc.tmpl('Subscription');
The above syntax is just an example; we haven't decided on a final syntax.
Beta Was this translation helpful? Give feedback.
All reactions
-
Thanks for the response, @kyleconroy. Maybe a template would help - but I'm not sure it would completely solve it. I think there may be multiple issues.
Here is a somewhat stripped down example:
CREATE TABLE places ( id UUID PRIMARY KEY, geometry GEOMETRY NOT NULL ); -- name: CreatePlace :one INSERT INTO places ( id, geometry ) VALUES ( $1, ST_GeomFromWKB($2)::geometry -- This is issue #1 ) RETURNING id, ST_AsBinary(geometry::geometry) as geometry; -- This is issue #2
This generates the expected Place type:
type Place struct { ID uuid.UUID `json:"id"` Geometry *geo.Geometry `json:"geometry"` }
However, these types were a surprise to me:
type CreatePlaceParams struct { ID uuid.UUID `json:"id"` StGeomfromwkb interface{} `json:"st_geomfromwkb"` // this is issue #1 } type CreatePlaceRow struct { ID uuid.UUID `json:"id"` Geometry interface{} `json:"geometry"` // this is issue #2 }
The first surprise (issue 1) was that the CreatePlaceParams struct has a StGeomfromwkb field of type interface{}. Though Postgres doesn't need it, I thought the ::geometry in ST_GeomFromWKB(2ドル)::geometry would be a hint to sqlc to use the override for the geometry type. So I expected *geom.Geometry there instead of interface{}.
The second surprise (issue 2) was that the CreatePlaceRow struct has a Geometry field of type interface{}. Postgres doesn't need the ::geometry in the query, and I understand that sqlc cannot know what the return from ST_AsBinary is, but I can't see how else to use the scanner on my *geom.Geometry type.
The underlying problem is that the driver I'm using doesn't seem to let me configure both the result and parameter format as binary. So I need to use ST_AsBinary for results and ST_GeomFromWKB on parameters. Unfortunately, this is messing with sqlc's parsing and ability to deduce types.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 3