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 :(
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
