1

For some reason, the prepareStatement I built in go using pgx is not returning any results/rows (see scenario1). In scenario 2, if I build the same query using Sprintf and $, the results are also not returned.

But, as shown in scenario 3, if I build the query with a Sprintf with %s placeholders and execute it, it will return results/rows.

I would like to build/execute the query using the "logic" of scenario 1 (with prepared statement instead with Sprintf). Any ideas?

I tested the query directly on the DB (with pgadmin) and it has data/results.

Assumption: I suspect it might have something to do with:

  • in the WHERE condition: the uuid data type or with the string in the IN condition
  • the query result that returns a uuid and a jsonb

Details: The DB is postgres and the table t has, among others, the field types:

  • t.str1 and t.properties are string
  • t.my_uuid is uuid.UUID (https://github.com/satori/go.uuid)
  • t.C is jsonb
  • the pgx version I'm using is: github.com/jackc/pgx v3.6.2+incompatible

scenario 1

func prepareQuery(cp *pgx.ConnPool) (string, error) {
    prep := `SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = $1
        AND t.properties IN ($2)
        AND t.my_uuid = $3`

    _, err := cp.Prepare("queryPrep", prep)
    if err != nil {
        return "some error", err
    }
    return prep, nil
}

func x() {
   //...
    q, err := prepareQuery(cPool)
    if err != nil {
        return fmt.Errorf("failed to prepare query %s: %w", q, err)
    }

    rows, err := cPool.Query("queryPrep", "DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    if err != nil {
        //return error...
    }

    for rows.Next() {   // zero rows
        //Scan...
    }
   //...
}

scenario 2

func makeQueryStr()string {
    return fmt.Sprintf(`SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = $1
        AND t.properties IN ($2)
        AND t.my_uuid = $3`)
}

func x() {
   //...
    myQuery := makeQueryStr()
    rows, err := cPool.Query(myQuery, "DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    if err != nil {
        //return error...
    }

    for rows.Next() {   // zero rows
        //Scan...
    }
   //...
}

scenario 3

func makeQueryStr(par1 string, par2 string, par3 uuid.UUID)string {
    return fmt.Sprintf(`SELECT DISTINCT
        A,B,C
        FROM table t
        WHERE t.str1 = '%s'
        AND t.properties IN (%s)
        AND t.my_uuid = '%s'`, par1, par2, par3)
}

func x() {
   //...
    myQuery := makeQueryStr("DumyStr", "'prop1','prop2'", myUUID) //myUUID is a valid satori uuid
    rows, err := cPool.Query(myQuery)
    if err != nil {
        //return error...
    }

    for rows.Next() {   // 10 rows
        //Scan...
    }
   //...
}
JonyD
  • 1,237
  • 3
  • 21
  • 34
  • @Flimzy sure. code updated. I just put the _ here because it was not relevant for the problem. – JonyD Jul 27 '21 at 16:28
  • 1
    The result of both scenrio 1 and 2 is `IN ('\'prop1\',\'prop2\'') ` (or however single quotes are escaped), i.e. you're passing in a single string even though you obviously intend to pass two separate strings. – mkopriva Jul 27 '21 at 16:45
  • 1
    You can use `t.properties = ANY ($2::text[])` and pass in a slice of strings with the help of types like `pq.Array` (I'm sure `pgx` has its equivalent). Or, if you want to keep using `IN` you'll need to build a comma-separated string of `$N` placeholders and a corresponding slice of parameters that includes the other parameters as well and pass all of them to `Query`, with `...` trailing the parameters argument. – mkopriva Jul 27 '21 at 16:52
  • See an example: https://stackoverflow.com/questions/38036752/go-and-in-clause-in-postgres it's not pgx specific but still a relevant example and if just search a bit you are bound to fine more. – mkopriva Jul 27 '21 at 16:54
  • 1
    you are supposed to use the prepared statement to execute the query..An example can be found here https://github.com/jackc/pgx/blob/cabb58cc40540ab9deb123264cb6c112cdb6e25b/stdlib/sql_test.go#L793 – Wishwa Perera Jul 27 '21 at 17:05
  • 1
    @mkopriva thank you. You were right. It was a problem with the string array. The `ANY` suggestion works. @WishwaPerera thanks but the Prepare method you mentioned is part of database/sql/sql.go (*DB) .The one I'm using belongs to conn_pool.go (*pgx.ConnPool). I don't know about *DB but the one from ConnPool can be called in a Query() by it's name / key in the Conn.preparedStatements map -- no "real" sql query needed. – JonyD Jul 27 '21 at 18:00

0 Answers0