Cursor within transaction behaves differently between go and psql

Question

I have a problem. For some reason, that is completely beyond me, the cursor I've created behaves differently when I run it in psql (where it works), as opposed to when I run it through my go code.

I take in a slice of names and pass them as a pq.Array to the transaction execute function call. From what I've read, this should do the trick for passing multiple values of arbitrary count to a query. But apparently not.. I don't know if this is happening because it is within a cursor in a transaction, that is the only thing I can think of anymore.

The idea is to query for all id's of tenants whose names do NOT appear in this list, hence using the <> operator. These names are unique in the database.

When I run it through the go code, I end up getting all the id's, not just the ones that do not appear in the list.

I've included the function here, the transaction is started in a calling function and passed to this one. Do tell if need to include something else for further clarification!

Am I missing something?

func DeleteTenants(orphanTenants []string, tx *sql.Tx) error {
    query := `
    DECLARE tenants_cursor CURSOR FOR
    SELECT id FROM tenants
    WHERE name <> ALL(ARRAY[$1])
    FOR UPDATE
    `
    if _, err := tx.Exec(query, pq.Array(orphanTenants)); err != nil {
        log.Println("DeleteTenants: Error creating Tenant cursor")
        return err
    }
    defer tx.Exec("CLOSE tenants_cursor")
    for {
        id := -1
        if err := tx.QueryRow("FETCH NEXT FROM tenants_cursor").Scan(&id); err != nil {
            if err == sql.ErrNoRows {
                log.Println("No rows found in tenants_cursor")
                break
            }
            log.Println("DeleteTenants: Error when fetching next row from Tenant cursor")
            return err
        }
        log.Println("Tenant ID to be deleted:", id)
        if err := deleteTenant(id, tx); err != nil {
            log.Println("DeleteTenants: Error returned from deleteTenant")
        }
    }
    return nil
}

Answer 1

Nevermind.. figured it out. I don't need to include the ARRAY notation within the query when passing pq.Array to the query.

This is embarrassing! Answer my own question within minutes.

So the query would be like this:

query := `
DECLARE tenants_cursor CURSOR FOR
SELECT id FROM tenants
WHERE name <> ALL($1)
FOR UPDATE
`
if _, err := tx.Exec(query, pq.Array(orphanTenants)); err != nil {
    log.Println("DeleteTenants: Error creating Tenant cursor")
    return err
}
defer tx.Exec("CLOSE tenants_cursor")