Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote: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.
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.
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.
hackers seems like the place to go then -- I consider it an RFE rather than a bug.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.
Thanks Michael
BJ
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
