Re: Function for retreiving datatype

Michael Fuhr wrote:

>On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
>
>
>
>>Actually I'm looking for an internal function -- something within
>>postgres' implementation of SQL itself, which I can use in queries
>>independent of the front-end language. The same way you use functions
>>like to_char() or now().
>>
>>
>
>Can you provide a hypothetical example of how you'd use this function?
>The problem is still vague enough to have different solutions
>depending on what you're trying to do. For example, if you want
>to know the column types of a table then you could query the system
>catalogs; but if you want to know the type of an arbitrary column
>of an arbitrarily complex query then I'm not aware of a way to get
>it in SQL (that doesn't mean there isn't a way, it just means that
>I don't know of a way). You could, however, write a C function
>that takes an "anyelement" argument and returns its type's OID.
>Here's an example using a trivial coltype() function that I just
>wrote:
>
>SELECT coltype(1) AS typeoid, coltype(1)::regtype AS typename;
> typeoid | typename
>---------+----------
> 23 | integer
>(1 row)
>
>SELECT coltype(1.234) AS typeoid, coltype(1.234)::regtype AS typename;
> typeoid | typename
>---------+----------
> 1700 | numeric
>(1 row)
>
>CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT, birthday DATE);
>INSERT INTO foo (name, birthday) VALUES ('Johnny', '2005-01-02');
>SELECT id, coltype(id)::regtype AS idtype,
> name, coltype(name)::regtype AS nametype,
> birthday, coltype(birthday)::regtype AS birthdaytype
>FROM foo;
> id | idtype | name | nametype | birthday | birthdaytype
>----+---------+--------+----------+------------+--------------
> 1 | integer | Johnny | text | 2005-01-02 | date
>(1 row)
>
>Is that anything like what you want? If not, then please be more
>specific about a particular problem you're trying to solve.
>
>
>
Your coltype() function is exactly what I'm looking for. I'd envisaged
something that takes an anyelement argument and returns the type as
text, but returning the OID is even better.

Can you please provide the source for the function?