Re: Function for retreiving datatype

From: Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Function for retreiving datatype
Date: 2005-01-10 21:19:34
Message-ID: 41E2F166.7070204@blakjak.sytes.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Michael Fuhr wrote:
<blockquote cite="mid20050110191604(dot)GA5387(at)winnie(dot)fuhr(dot)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 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.
</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.&nbsp; 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.<br>
<br>
But that's getting away from the point.&nbsp; It doesn't really matter
whether I could have used gettype() to solve that particular problem.&nbsp;
Which is why I didn't bring it up in my original post.&nbsp; My post was all
about finding out whether postgres has this functionality.&nbsp; If it does,
and I just wasn't looking hard enough, it's all good.&nbsp; If it doesn't,
I'd like to explore the possibility of getting it added in.<br>
<br>
<blockquote cite="mid20050110191604(dot)GA5387(at)winnie(dot)fuhr(dot)org" type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">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.
</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>
Thanks Michael<br>
<br>
BJ<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.0 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GUNDUZ 2005-01-10 21:21:18 Re: Link to development version of docs on website?
Previous Message Chris 2005-01-10 21:15:40 Link to development version of docs on website?