Обсуждение: Tablefunc crosstab error messages
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
On Mon, Aug 27, 2012 at 08:07:02PM -0400, Mali Akmanalp wrote: > 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. That would prove helpful, +1 from me. > 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? Yes. Accepting the query as proper SQL syntax would require modifying the backend grammar. Extensions cannot do that, but they can define functions accepting a string and run it as a SQL command.