An OID(Object identifier) is a unique number (primary key) given to the database objects like functions, procedures, triggers, event triggers, tables, views, etc. *The doc explains an OID.
*In this post, I introduce many examples with a function but you can also use these examples with a procedure.
For example, you create my_func()
function as shown below:
CREATE FUNCTION my_func(v1 INT, v2 INT) RETURNS INT
AS $$
BEGIN
RETURN v1 + v2;
END;
$$ LANGUAGE plpgsql;
Now, you can get the OID of my_func()
with regproc
type, regprocedure
type, to_regproc()
or to_regprocedure()
and oid
type as shown below:
postgres=# SELECT 'public.my_func'::regproc::oid;
oid
-------
26878
(1 row)
Or:
postgres=# SELECT 'public.my_func(INT, INT)'::regprocedure::oid;
oid
-------
26878
(1 row)
Or:
postgres=# SELECT to_regproc('my_func')::oid;
to_regproc
------------
26878
(1 row)
Or:
postgres=# SELECT to_regprocedure('public.my_func(INT, INT)')::oid;
to_regprocedure
-----------------
26878
(1 row)
Or:
postgres=# SELECT CAST(CAST('public.my_func' AS regproc) AS oid);
oid
-------
26878
(1 row)
Or:
postgres=# SELECT CAST(CAST('public.my_func(INT, INT)' AS regprocedure) AS oid);
oid
-------
26878
(1 row)
Or:
postgres=# SELECT CAST(to_regproc('my_func') AS oid);
to_regproc
------------
26878
(1 row)
Or:
postgres=# SELECT CAST(to_regprocedure('public.my_func(INT, INT)') AS oid);
to_regprocedure
-----------------
26878
(1 row)
*Memos:
You must do type conversion with
::
orCAST()
to get the OID offunc()
. *My answer explains how to do type conversion.You can omit the schema
public.
.-
regproc
:- must be used with an unoverloaded function or procedure otherwise there is error.
- must be used with a function or procedure name without
()
,(INTENGER)
, etc like the example abovemy_func
otherwise there is error.
-
regprocedure
:- must be used with an overloaded or unoverloaded function or procedure otherwise there is error.
- must be used with a function or procedure name with
()
,(INTENGER)
, etc like the example abovefunc(INTENGER, INTENGER)
otherwise there is error.
There are more types
regclass
,regcollation
,regconfig
, etc according to the doc.-
to_regproc()
:- must be used with an unoverloaded function or procedure otherwise the OID is not returned. *There is no error.
- must be used with a function or procedure name without
()
,(INTENGER)
, etc like the example abovemy_func
otherwise the OID is not returned. *There is no error.
-
to_regprocedure()
:- must be used with an overloaded or unoverloaded function or procedure otherwise the OID is not returned. *There is no error.
- must be used with a function or procedure name with
()
,(INTENGER)
, etc like the example abovefunc(INTENGER, INTENGER)
otherwise the OID is not returned. *There is no error.
The doc explains
to_regproc()
andto_regprocedure()
.My answer explains how to get the OID of a table.
In addition, not using oid
type cannot get the OID of my_func()
as shown below:
postgres=# SELECT 'public.my_func'::regproc;
regproc
---------
my_func
(1 row)
And, there are some cases which you can omit oid
type as shown below:
postgres=# SELECT prosrc FROM pg_proc WHERE oid = 'public.my_func'::regproc;
prosrc
-------------------
+
BEGIN +
RETURN v1 + v2;+
END; +
(1 row)
postgres=# SELECT pg_get_functiondef('public.my_func'::regproc);
pg_get_functiondef
-------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.my_func(v1 integer, v2 integer)+
RETURNS integer +
LANGUAGE plpgsql +
AS $function$ +
BEGIN +
RETURN v1 + v2; +
END; +
$function$ +
(1 row)
*Memos:
- Setting
oid
type for the examples above gets the same results. - My answer explains the example above.
And, you can still get the OID
of my_func()
from pg_proc without regproc
type, regprocedure
type, to_regproc()
or to_regprocedure()
and oid
type as shown below:
postgres=# SELECT oid FROM pg_proc WHERE proname = 'my_func';
oid
-------
26878
(1 row)
Top comments (0)