Re: Function for retreiving datatype

Michael Fuhr wrote: <blockquote cite="mid20050110191604.GA5387@winnie.fuhr.org" type="cite"><pre wrap="">On Tue, Jan
11,2005 at 05:26:59AM +1100, Brendan Jurd wrote: </pre><blockquote type="cite"><pre wrap="">The original problem had to
dowith 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.   </pre></blockquote><pre wrap="">
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.
 </pre></blockquote> Not really an issue.  I could have yanked the source query out of the row-returning function,
plantedit into a regular console, and wrapped the hypothetical gettype() function around the individual columns to test
thetype of their output.<br /><br /> But that's getting away from the point.  It doesn't really matter whether I could
haveused gettype() to solve that particular problem.  Which is why I didn't bring it up in my original post.  My post
wasall about finding out whether postgres has this functionality.  If it does, and I just wasn't looking hard enough,
it'sall good.  If it doesn't, I'd like to explore the possibility of getting it added in.<br /><br /><blockquote
cite="mid20050110191604.GA5387@winnie.fuhr.org"type="cite"><pre wrap=""></pre><blockquote type="cite"><pre wrap="">On
thatnote, 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.   </pre></blockquote><pre wrap="">
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.
 </pre></blockquote> hackers seems like the place to go then -- I definitely don't consider it a bug.<br /><br />
ThanksMichael<br /><br /> BJ<br />