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