Repository navigation
Unable to use type Overrides feature #950
Description
hello, I am having trouble getting the type overrides features to work.
I have a table that looks like this:
CREATE TABLE IF NOT EXISTS cool_table (
cool_id uuid NOT NULL,
join_id text NOT NULL,
valid_from timestamp with time zone,
valid_to timestamp with time zone,
revision int NOT NULL,
user_id uuid NOT NULL,
username text NOT NULL,
PRIMARY KEY(cool_id, valid_to),
CONSTRAINT cool_constraint UNIQUE (cool_id,join_id)
);
then I have the following query:
-- name: GetCoolThing :one
SELECT t.*
FROM cool_table jt
JOIN diff_table t ON jt.joint_id = t.id
WHERE jt.cool_id = sqlc.arg(cool_id)::uuid
AND sqlc.arg(target_time)::timestamptz IS NULL OR jt.valid_from < sqlc.arg(target_time)::timestamptz
AND sqlc.arg(target_time)::timestamptz IS NULL OR jt.valid_to > sqlc.arg(target_time)::timestamptz;
This ultimately generates this code (about what you would expect):
const getCoolThing = `-- name: GetCoolThing :one
SELECT t.id, t.name, t.factory_id, t.created, t.deleted, t.updated
FROM cool_table jt
JOIN diff_table t ON jt.diff_type = t.id
WHERE jt.cool_id = $1::uuid
AND $2::timestamptz IS NULL OR jt.valid_from < $2::timestamptz
AND $2::timestamptz IS NULL OR jt.valid_to > $2::timestamptz
`
type GetCoolThingParams struct {
CoolID uuid.UUID
TargetTime time.Time
}
func (q *Queries) GetGetCoolThing(ctx context.Context, arg GetGetCoolThingParams) (CoolType, error) {
row := q.db.QueryRowContext(ctx, getGetCoolThing, arg.CoolID, arg.TargetTime)
var i CoolType
err := row.Scan(
&i.ID,
&i.Name,
&i.FactoryID,
&i.Created,
&i.Deleted,
&i.Updated,
)
return i, err
}
This query does not work as intended because it assumes TargetTime can be nil but the generated code does not allow for that to happen because the zero type of time.Time is not nil. So I tried to use the override tool:
version: "1"
packages:
- name: "postgres"
path: "postgres"
emit_interface: true
schema: "db/migrations/"
queries: "db/queries/"
overrides:
- column: "cool_table.valid_from"
go_type:
import: "time"
package: "time"
type: "Time"
pointer: true
nullable: true
- column: "cool_table.valid_to"
go_type:
import: "time"
package: "time"
type: "Time"
pointer: true
nullable: true
however, this does not change the generated code at all. I also tried to do a type override and this also didn't work. I have also tried making this a package-level thing and that also doesn't work. Any tips here?
Activity
jskelcy commentedon Mar 19, 2021
To be clear I am using sqlc 1.7
jskelcy commentedon Mar 24, 2021
Alright, I have spent more time looking into this after stepping away for a little while and the problem has become apparent. it would appear the
internal/compiler/resolve.gowe are creating some "istate" which we then pass to the variouscodegenpackages for the relevant generation. The issue appears to be that by the time thecodegenpackages are run the "column name" is the name of the sqlc variable rather than the column for which the sqlc variable is referring to. So in the above example where the table is:and the query is:
By the time that here the column name is whatever the variable is. So in the above example, it would be
target_timewhich is not a column in the table, so when this function is run it will try to comparetarget_timeto the list overrides which does not work.jskelcy commentedon Mar 24, 2021
Given what I learned above I tried something else, which was to change my
sqlc.yamlfile to this:which actually works as expected. This is the best current workaround for this bug.