Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

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