sqlpostgresqlgosqlc

Return values consisting of the set in Posgresql

I use https://github.com/kyleconroy/sqlc to generate code. I want to return human_id using the group_id array.

-- name: HumansByGroupID :many
SELECT human_id FROM groups
WHERE group_id IN (UNNEST($1::uuid[]));

return ERROR: set-returning functions are not allowed in WHERE (SQLSTATE 0A000)

In my understanding, ... IN (UNNEST($1::uuid[])); turns into IN ('...','...','...');

Solution

  • You can do the following:

    WHERE group_id IN (SELECT col FROM UNNEST($1::uuid[]) AS col);

    or, alternatively, you can also do:

    WHERE group_id = ANY ($1::uuid[]);