我遇到了 SQLC 生成连接和数组的错误。

huangapple go评论86阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2021年10月20日 12:32:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/69640362.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定