An OID(Object identifier) in PostgreSQL

Buy Me a Coffee☕

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 :: or CAST() to get the OID of func(). *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 above my_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 above func(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 above my_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 above func(INTENGER, INTENGER) otherwise the OID is not returned. *There is no error.

• The doc explains to_regproc() and to_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)