DEV Community

Super Kai (Kazuya Ito)
Super Kai (Kazuya Ito)

Posted on • Edited on

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT 'public.my_func(INT, INT)'::regprocedure::oid;
  oid
-------
 26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT to_regproc('my_func')::oid;
 to_regproc
------------
      26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT to_regprocedure('public.my_func(INT, INT)')::oid;
 to_regprocedure
-----------------
           26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT CAST(CAST('public.my_func' AS regproc) AS oid);
  oid
-------
 26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT CAST(CAST('public.my_func(INT, INT)' AS regprocedure) AS oid);
  oid
-------
 26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT CAST(to_regproc('my_func') AS oid);
 to_regproc
------------
      26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

Or:

postgres=# SELECT CAST(to_regprocedure('public.my_func(INT, INT)') AS oid);
 to_regprocedure
-----------------
           26878
(1 row)
Enter fullscreen mode Exit fullscreen mode

*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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
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)
Enter fullscreen mode Exit fullscreen mode

*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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)