PostgreSQL – How to know specific name or OID of a function/procedure that was just created?

Question

If a CREATE FUNCTION/PROCEDURE is executed, is there any way to get the oid or the specific name of the object that was just created?

It seems the only way is to look tediously look at the parameter lists for each function/procedure as listed in the system catalogs or information_schema, and compare until you find an exact match?

Answer 1

You can use the system object identifier types for this:

The OID alias types have no operations of their own except for specialized input and output routines. These routines are able to accept and display symbolic names for system objects, rather than the raw numeric value that type oid would use. The alias types allow simplified lookup of OID values for objects. […] The […] input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically.

In particular, to get the the oids of functions or procedures, you'd use regprocedure, e.g.

SELECT 'public.test()'::regprocedure::oid;
SELECT 'public.test(int, int)'::regprocedure::oid;

^{(online demo)}

Answer 2

You can get the OID of my_func() function with regproc type, regprocedure type, to_regproc() or to_regprocedure() and oid type as shown below:

SELECT 'public.my_func'::regproc::oid;

Or:

SELECT 'public.my_func(INTEGER, INTEGER)'::regprocedure::oid;

Or:

SELECT to_regproc('my_func')::oid;

Or:

SELECT to_regprocedure('public.my_func(INTEGER, INTEGER)')::oid;

*Memos:

• You can also use these ways above with a procedure.