Common syntax for searching IN an array of possible values

[starius]

What do you want to change?

We develop a software which runs against sqlite3 and postgres and we use sqlc to generate DB clients.

We have queries like ... WHERE id IN (<id1>, <id2>, ...) where the list of IDs is passed as a parameter. Currently we can't use the same source SQL file to generate wrappers for both sqlite3 and postgres, since they have different syntax for this use case:

-- Sqlite:
WHERE id IN (sqlc.slice('ids'));

-- Postgres:
WHERE id = ANY(sqlc.arg('ids')::int[]);

This is documented in https://docs.sqlc.dev/en/stable/howto/select.html#passing-a-slice-as-a-parameter-to-a-query

I would like to have a common syntax which would be translated to DB specific queries by SQLC. For example, SQLC could recognize WHERE id IN (sqlc.slice('ids')) pattern and translate it to WHERE id = ANY(sqlc.arg('ids')::int[]) in case of postgres. This might be done as a preprocessing.

I volunteer to implement this myself if I get a concept ACK.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go