Trouble using function in select and insert

[tschaub]

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.

[kyleconroy]

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.

[tschaub]

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.