英文:
I got error SQLC generate with join, array
问题
我正在开发一个使用postgreSQL、golang和sqlc的API服务器。
我的SQL语句如下:
SELECT e.*, rslt.artineve FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt;
而sqlc generate
将其翻译为以下代码:
const getAllEvents = `-- name: GetAllEvents :many
SELECT e.uid, e.name, e.intro, e.phone, e.address, e.price, e.explain, e.uri, e.images, e.start_date, e.end_date, e.time_info, e.sns, e.genre, e.longitude, e.latitude, e.live, e.created_at, rslt.artineve FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt
`
type GetAllEventsRow struct {
Uid int64 `json:"uid"`
TimeInfo string `json:"time_info"`
Sns json.RawMessage `json:"sns"`
Genre []string `json:"genre"`
CreatedAt time.Time `json:"created_at"`
Artineve interface{} `json:"artineve"`
}
func (q *Queries) GetAllEvents(ctx context.Context) ([]GetAllEventsRow, error) {
rows, err := q.db.QueryContext(ctx, getAllEvents)
if err != nil {
return nil, err
}
defer rows.Close()
var items []GetAllEventsRow
for rows.Next() {
var i GetAllEventsRow
if err := rows.Scan(
&i.Uid,
&i.Name,
&i.Sns,
pq.Array(&i.Genre),
&i.CreatedAt,
&i.Artineve,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
在TablePlus应用程序中进行测试时,它是有效的。
但是,API服务器返回的结果类似于"artineve": "ezIsMTh9"
,但应该是[11, 3, 4]
。
英文:
I am developing an API server with postgreSQL, golang, sqlc.
My sql is as follows
SELECT e.*, rslt.artineve FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt;
And sqlc generate
translates it as follows
const getAllEvents = `-- name: GetAllEvents :many
SELECT e.uid, e.name, e.intro, e.phone, e.address, e.price, e.explain, e.uri, e.images, e.start_date, e.end_date, e.time_info, e.sns, e.genre, e.longitude, e.latitude, e.live, e.created_at, rslt.artineve FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt
`
type GetAllEventsRow struct {
Uid int64 `json:"uid"`
TimeInfo string `json:"time_info"`
Sns json.RawMessage `json:"sns"`
Genre []string `json:"genre"`
CreatedAt time.Time `json:"created_at"`
Artineve interface{} `json:"artineve"`
}
func (q *Queries) GetAllEvents(ctx context.Context) ([]GetAllEventsRow, error) {
rows, err := q.db.QueryContext(ctx, getAllEvents)
if err != nil {
return nil, err
}
defer rows.Close()
var items []GetAllEventsRow
for rows.Next() {
var i GetAllEventsRow
if err := rows.Scan(
&i.Uid,
&i.Name,
&i.Sns,
pq.Array(&i.Genre),
&i.CreatedAt,
&i.Artineve,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
And it works when I test in TablePlus App.
But, the result from API server returns like "artineve": "ezIsMTh9"
, but it must be like [11, 3, 4]
.
答案1
得分: 1
添加类型提示解决了这个问题。
SELECT e.*, rslt.artineve::integer[] FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt;
英文:
Add a type hint solved this issue.
SELECT e.*, rslt.artineve::integer[] FROM events AS e, LATERAL (
SELECT ARRAY (SELECT art_id FROM arteve WHERE arteve.eve_id = e.uid) AS artineve
) rslt;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论