Support dynamic sized IN clauses

You should add support for a query with a dynamically sized list of values for an IN clause in a WHERE segment. This would be useful for when you are using a dataloader to get data from the database. This is most useful for graphql servers.

See the large example on this page for a use-case: https://gqlgen.com/reference/dataloaders/

I am thinking something like:

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id IN ($);

would generate a go function that takes in a slice of values for the input. This is distinguished from just wanting a single value by only having the $ instead of $1.

You're in luck! sqlc already supports this using ANY. The following SQL:

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id = ANY($1::int[]);

generate this method signature

func (q *Queries) GetPeopleMany(ctx context.Context, ids []int) ([]People, error) {
  ...
}

Any way for this to work with mysql as well? Tried ANY(?::int[]) but it reports a syntax error.

@kyleconroy

Any way for this to work with mysql as well? Tried ANY(?::int[]) but it reports a syntax error.

@kyleconroy I have the same issue, should this work for mysql or is it only available in postgres?

Ditto, but sqlite.

please use sqlc.slice()
https://play.sqlc.dev/p/ed1b1d235f3a32c811a27dec68d826b4c0c36dedf0e614f0339fa02e11cc4ba4

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id = ANY($1::int[]);

This won't work with enum type

-- name: GetPeopleMany :many SELECT * FROM people WHERE id = ANY($1::int[]);

This won't work with enum type

At the moment(Apr 2024) it works with enum types. You just need to specify the enum type (e.g. ANY($1::MyEnum[]))

@mbesida

@mbesida This cause error:

failed to encode args[n]: unable to encode []CustomEnumType{\"v1\"} into text format for unknown type (OID 1234): cannot find encode plan

I used it this way and it works like charm.

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id IN (sqlc.slice('ids'));

Navigation Menu

Repository navigation

Support dynamic sized IN clauses #167

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Support dynamic sized IN clauses #167

Description

Activity

kyleconroy commented on Dec 13, 2019

markschmid commented on Nov 21, 2020

SebastienMelki commented on Jul 25, 2022

josharian commented on Jul 28, 2023

orisano commented on Jul 29, 2023

iceteahh commented on Dec 27, 2023

mbesida commented on Apr 4, 2024

psnehanshu commented on May 8, 2025

BijaySharma commented on May 29, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions