An OID(Object identifier) in PostgreSQL
Tags: postgres, oid, objectidentifier, id
Published: 2024-01-18 01:58:51

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](https://www.postgresql.org/docs/current/datatype-oid.html) 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:

```sql
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:

```sql
postgres=# SELECT 'public.my_func'::regproc::oid;
  oid
-------
 26878
(1 row)
```

Or:

```sql
postgres=# SELECT 'public.my_func(INT, INT)'::regprocedure::oid;
  oid
-------
 26878
(1 row)
```

Or:

```sql
postgres=# SELECT to_regproc('my_func')::oid;
 to_regproc
------------
      26878
(1 row)
```

Or:

```sql
postgres=# SELECT to_regprocedure('public.my_func(INT, INT)')::oid;
 to_regprocedure
-----------------
           26878
(1 row)
```

Or:

```sql
postgres=# SELECT CAST(CAST('public.my_func' AS regproc) AS oid);
  oid
-------
 26878
(1 row)
```

Or:

```sql
postgres=# SELECT CAST(CAST('public.my_func(INT, INT)' AS regprocedure) AS oid);
  oid
-------
 26878
(1 row)
```

Or:

```sql
postgres=# SELECT CAST(to_regproc('my_func') AS oid);
 to_regproc
------------
      26878
(1 row)
```

Or:

```sql
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](https://stackoverflow.com/questions/13809547/convert-integer-to-string-in-postgresql#77723276) 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](https://www.postgresql.org/docs/current/datatype-oid.html).

- `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](https://www.postgresql.org/docs/current/functions-info.html) explains `to_regproc()` and `to_regprocedure()`.

- [My answer](https://stackoverflow.com/questions/10953123/how-to-get-the-oid-of-a-postgres-table/77846986#77846986) 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:

```sql
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:

```sql
postgres=# SELECT prosrc FROM pg_proc WHERE oid = 'public.my_func'::regproc;
      prosrc
-------------------
                  +
 BEGIN            +
   RETURN v1 + v2;+
 END;             +

(1 row)
```

```sql
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](https://stackoverflow.com/questions/3524859/show-full-stored-function-or-procedure-code-in-postgresql#77836599) explains the example above.

And, you can still get the `OID` of `my_func()` from [pg_proc](https://www.postgresql.org/docs/current/catalog-pg-proc.html) without `regproc` type, `regprocedure` type, `to_regproc()` or `to_regprocedure()` and `oid` type as shown below:

```sql
postgres=# SELECT oid FROM pg_proc WHERE proname = 'my_func';
  oid
-------
 26878
(1 row)
```