我在使用join，array生成SQLC时出现错误

Question

我正在用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 server返回的结果类似于"artineve": "ezIsMTh9"，但它必须类似于[11, 3, 4]。

Answer 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;