Hi All,
I was recently struggling with getting a large crosstab (
http://www.postgresql.org/docs/current/static/tablefunc.html) query to work
and noticed the error messages were not super helpful: "return and sql
tuple descriptions are incompatible".
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
