Hi All,
I had to manually take the query into pieces and check each type, because it wouldn't tell me which types were not matching. This is not fun especially when you're using an ORM and composing queries piece by piece. Reading into contrib/tablefunc/tablefunc.c, it looks like this shouldn't be too hard to fix.
crosstab() calls compatCrosstabTupleDescs():
1545 /*
1546 * - attribute [1] of the sql tuple is the category; no need to check it -
1547 * attribute [2] of the sql tuple should match attributes [1] to [natts]
1548 * of the return tuple
1549 */
1550 sql_attr = sql_tupdesc->attrs[2];
1551 for (i = 1; i < ret_tupdesc->natts; i++)
1552 {
1553 ret_attr = ret_tupdesc->attrs[i];
1554
1555 if (ret_attr->atttypid != sql_attr->atttypid)
1556 return false;
1557 }
Returning the type information to the caller seems like a pain but compatCrosstabTupleDescs already has instances in it where it fails with an error message, so I propose we just do that and tell the user the expected and actual types here, instead of returning false here and throwing a generic error message in the caller.
What do you think? Let me know so I can write up a patch for you guys.
Also, just curious, is the reason the query passed into crosstab is a string (rather than an SQL expression) that it's just easier to implement that way?
Cheers,
~Mali
