0

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].

MoonTaeTae
  • 51
  • 6

1 Answers1

1

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;
MoonTaeTae
  • 51
  • 6