How to get the function name of a regprocedure in PostgreSQL?

Question

I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:

CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
 RETURNS text AS
$func$
 SELECT proname::text
 FROM pg_catalog.pg_proc AS p
 JOIN pg_catalog.pg_namespace AS ns
 ON p.pronamespace = ns.oid
 WHERE p.oid = _fn;
$func$ LANGUAGE sql;

I expect to get my_func in return for

SELECT get_funcname('my_func(text, variadic text[])');

However, the above is giving me the error:

ERROR: syntax error at or near "variadic"
LINE 1: SELECT get_funcname('my_func(text, variadic text[])');
 ^
CONTEXT: invalid type name "variadic text[]"

Can anyone help explain how to fix the function or syntax?

Answer 1

You have

ERROR: syntax error at or near "variadic"

From the docs,

VARIADIC parameters are input parameters, but are treated specially as described next.

That means you don't do anything special for VARIADIC parameters beyond what you would otherwise specify for input params. In your case, just drop that token,

SELECT get_funcname('my_func(text, text[])');

Can anyone help explain how to fix the function or syntax?

Sure.

WHERE p.oid = regexp_replace(_fn, ' VARIADIC ', ' ', 'i');