Re: Function for retreiving datatype

Michael Fuhr wrote:
> On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
>
>>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.

[...snip slick function...]

> Now watch, somebody will jump in and say, "Why'd you go to all that
> trouble?  Here's an easier way...."

Not exactly a drop in replacement, but you could check whether you have
one of set of types with the undocumented* IS OF construct:

regression=# select prosrc is of (text) from pg_proc limit 1;
  ?column?
----------
  t
(1 row)

regression=# select prosrc is of (bytea) from pg_proc limit 1;
  ?column?
----------
  f
(1 row)

regression=# select prosrc is of (bytea,text) from pg_proc limit 1;
  ?column?
----------
  t
(1 row)


Also note that in PL/pgSQL, you can use %TYPE to create a variable to
the same type as an argument:

   "%TYPE is particularly valuable in polymorphic functions, since the
    data types needed for internal variables may change from one call to
    the next. Appropriate variables can be created by applying %TYPE to
    the function's arguments or result placeholders."

http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE

And a variable can also be created with the function's runtime-resolved
return type:

   "When the return type of a PL/pgSQL function is declared as a
    polymorphic type (anyelement or anyarray), a special parameter $0 is
    created. Its data type is the actual return type of the function, as
    deduced from the actual input types (see Section 33.2.5). This allows
    the function to access its actual return type as shown in Section
    37.4.2. $0 is initialized to null and can be modified by the
    function, so it can be used to hold the return value if desired,
    though that is not required. $0 can also be given an alias."

http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES


Joe

* search the archives if you want the background as to why it is still
undocumented -- in short, it is close to, but not quite SQL99 compliant,
and although I had hoped to fix that "not quite" part, I've yet to find
the time :(