Michael Fuhr wrote:

On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:

The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query.  To do so, you need to provide a
list of column definitions.  I was getting the error about the returned
row types not matching my column defs.  In the end it was a simple
mistake -- I had specified 'text' where I should have specified
'varchar'.  I had thought to use some kind of "gettype" function to find
out exactly what data types my query was returning.


Where would you call this gettype() function from?  It seems like
you have a chicken-and-egg situation: you need to provide a column
definition list when you issue the query, but you don't know what
the return row will look like until the query executes the function.
In the current implementation, if a function returns SETOF RECORD
then you need to know in advance what columns a particular invocation
of that function will return.

Not really an issue. I could have yanked the source query out of the row-returning function, planted it into a regular console, and wrapped the hypothetical gettype() function around the individual columns to test the type of their output.

But that's getting away from the point. It doesn't really matter whether I could have used gettype() to solve that particular problem. Which is why I didn't bring it up in my original post. My post was all about finding out whether postgres has this functionality. If it does, and I just wasn't looking hard enough, it's all good. If it doesn't, I'd like to explore the possibility of getting it added in.

On that note, it might be helpful to increase the verbosity of the
"returned row types" error message, so that it actually explains the
mismatch it encountered.  Something like "Returned column 3 is
varchar(15) but column definition is text" would have made debugging a
whole lot easier.


Consider suggesting that to the developers.  I'm not sure what the
best list would be -- maybe pgsql-bugs if you consider the terse
message to be a bug, or maybe pgsql-hackers since it's a proposed
enhancement.

hackers seems like the place to go then -- I definitely don't consider it a bug.

Thanks Michael

BJ