Thread: Verbosity of column definition mismatch error

Originally posted in psql-general, submitted here as a prospective RFE:

When using a row-returning function (RETURNS SETOF record) in the FROM 
clause of a query, you have to supply a column definition list.  I was 
recently attempting to use a row-returning function in a query and 
received the following error message:

ERROR:  query-specified return row and actual function return row do not 
match

The function in question returned 8 columns, some of which used fairly 
lengthy expressions, and it took me quite a while to track down the 
problem -- it turned out that a column I was expecting to be 'text' was 
actually 'varchar(10)'.  In complex queries, isolating a single type 
mismatch can be something of a chore.

With that in mind, I'd like to submit a request, that the verbosity of 
this error message be increased to explain which ordinal column position 
the error occurred at, and, if possible, the details of the mismatch.  
In my case, a secondary message saying something like "function foo() 
column 4 returned type varchar(10), but column definition specifies type 
text" would have made the debugging process very smooth.

Is this a reasonable suggestion?

Regards
BJ

Brendan Jurd <blakjak@blakjak.sytes.net> writes:
> ERROR:  query-specified return row and actual function return row do not 
> match

> With that in mind, I'd like to submit a request, that the verbosity of 
> this error message be increased to explain which ordinal column position 
> the error occurred at, and, if possible, the details of the mismatch.  

This is a fairly trivial change, but unfortunately it's too late for
8.0, because we froze error message texts some time ago (for the benefit
of translators).

If you want to start working on a patch for 8.1, look in 
src/backend/executor/nodeFunctionscan.c.  I'd be inclined to forget the
bool return convention for tupledesc_match() and just have it ereport
directly when it sees a problem.  The rowtype matching code in
check_sql_fn_retval (in src/backend/executor/functions.c) would be
useful as a reference.
        regards, tom lane

Tom Lane wrote:

>Brendan Jurd <blakjak@blakjak.sytes.net> writes:
>  
>
>>ERROR:  query-specified return row and actual function return row do not 
>>match
>>    
>>
>
>  
>
>>With that in mind, I'd like to submit a request, that the verbosity of 
>>this error message be increased to explain which ordinal column position 
>>the error occurred at, and, if possible, the details of the mismatch.  
>>    
>>
>
>This is a fairly trivial change, but unfortunately it's too late for
>8.0, because we froze error message texts some time ago (for the benefit
>of translators).
>
>If you want to start working on a patch for 8.1, look in 
>src/backend/executor/nodeFunctionscan.c.  I'd be inclined to forget the
>bool return convention for tupledesc_match() and just have it ereport
>directly when it sees a problem.  The rowtype matching code in
>check_sql_fn_retval (in src/backend/executor/functions.c) would be
>useful as a reference.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>  
>
I'll check it out.  Thanks for the pointers.

Brendan Jurd <blakjak@blakjak.sytes.net> writes:
> While I was playing around in nodeFunctionscan.c, I noticed what seems 
> to be a minor bug in the latest CVS version.  If you get the column 
> definition wrong in your query, the error displays twice.

Not here.  I think you've got postmaster stderr pointed at your console.
        regards, tom lane