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

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more