issues when query by record type

[hoopsoul]

Hi, Here is my test code

var pg = require('pg')
pg.format = require('pg-format')
var conString = "...";
pg.connect(conString, function(err, client) {
  var query = `
    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[%L])
    ;`
  var params = [[1, 'Jack'], [2, 'Tom']]
  console.log(pg.format(query, params))
  client.query(pg.format(query, params), (err, data) => {
    console.log(err)
  })
})

The formatted query is

    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[('1', 'Jack'), ('2', 'Tom')])

Here are may 2 issues:

1. int type changed to varchar
2. it throw “cannot compare dissimilar column types integer and unknown at record column 1”

If change the query to

    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[('1'::int, 'Jack'::varchar), ('2'::int, 'Tom'::varchar)])

it could get right result.

Thanks

[rpedela]

The integers aren't changed to varchar, the single quotes tell PG that the value inside is a literal value regardless of data type and to treat it appropriately. However in this case, PG does not know how to interpret the literal value which is why it throws an error. The fix is the explicit cast.

Possible solutions

1. Do not surround numbers with single quotes, but this could lead to SQL injection and would defeat the entire purpose of this library.
2. Explicitly cast all types to the most generic type such as numeric for numbers and text for strings. However there are probably use cases where this is undesirable or incorrect. Admittedly, I cannot think of any examples off the top of my head.
3. Add some capability to the API to explicitly cast, but it is not obvious to me how to do this without making the API complicated which I want to avoid.

If you have ideas for option 3, then I am happy to discuss them. I think options 1 and 2 are too problematic to seriously consider. There is a workaround for your use case though.

Workaround
You can explicitly cast yourself, but the code is slightly more complicated.

var pg = require('pg');
pg.format = require('pg-format');

var params = [[1, 'Jack'], [2, 'Tom']];
var paramSql = [];

// escape literals using %L
for (var i = 0; i < params.length; i++) {
    var p = params[i];
    paramSql.push(pg.format('(%L::int, %L::varchar)', p[0], p[1]));
}

// insert escaped literal array into SQL query using %s
var sql = 'SELECT * FROM myTable WHERE (id, name) = ANY(ARRAY[%s])';
console.log(pg.format(sql, paramSql));

[hoopsoul]

Thanks, the workaround solved my issue :)
And for now, I have no good idea about option 3, I agree the library should not be too complicated, if I could figure out some simple way about it, I will open a new issue to discuss it :)
Thanks again.