Passing slice into an IN query

[taylow]

Hey, I've been using SQLC for a week or so now, and I'm really enjoying it. I have ran into a little problem, and I wanted to see if anyone had a solution!

I am trying to write a query that allows me to fetch multiple results based on a slice of inputs.

e.g.

SELECT *
FROM "workspace"
WHERE id IN ('1', '2', '3', ..., 'n');

I used the following query while generating my code using SQLC:

-- name: RetrieveWorkspaces :many
-- RetrieveWorkspaces fetches a list of Workspaces by their ID from the database
SELECT *
FROM "workspace"
WHERE id IN (sqlc.arg(ids)::UUID []);

The generated code looks like this:

const retrieveWorkspaces = `-- name: RetrieveWorkspaces :many
SELECT id, name, created_at, updated_at
FROM "workspace"
WHERE id IN ($1::UUID [])
`

// RetrieveWorkspaces fetches a list of Workspaces by their ID from the database
func (q *Queries) RetrieveWorkspaces(ctx context.Context, ids []uuid.UUID) ([]Workspace, error) {
	rows, err := q.db.QueryContext(ctx, retrieveWorkspaces, pq.Array(ids))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Workspace
	for rows.Next() {
		var i Workspace
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.CreatedAt,
			&i.UpdatedAt,
		); 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
}

But my tests fail with the following message from Postgres:

--- FAIL: TestRetrieveWorkspaces (0.02s)
    /pkg/database/sqlc/workspace_test.go:90: 
        	Error Trace:	workspace_test.go:90
        	Error:      	Received unexpected error:
        	            	pq: operator does not exist: uuid = uuid[]
        	Test:       	TestRetrieveWorkspaces

Am I missing something here? I also tried it without the sqlc.arg(ids)::UUID [] part, and just used $1::UUID [] but that resulted in the parameter being named dollar_1.

[taylow]

I have figured it out! I hadn't read this properly, and thought it was a different kind of query! I still had to rename the value using sqlc.arg(ids), as it was still being named dollar_1, but that's fine 😊

For reference, the following statement worked:

-- name: RetrieveWorkspaces :many
-- RetrieveWorkspaces fetches a list of Workspaces by their ID from the database
SELECT *
FROM "workspace"
WHERE id = ANY(sqlc.arg(ids)::uuid []);