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.
> 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.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/