pg_get_object_address()

A function returning OIDs for the specified object

pg_get_object_address() is a system function returning object identifiers for the provided type code, object name(s) and (optionally) argument arrays.

pg_get_object_address() was added in PostgreSQL 9.5.

Usage

pg_get_object_address ( type text, object_names text[], object_args text[] )
        → record ( classid oid, objid oid, objsubid integer )

The returned values correspond to the identifying columns used in pg_depend; to perform the inverse action, i.e. return the names of an object's type etc., see pg_identify_object_as_address().

The valid values for type are not explictly documented, but can be inferred with the following query:

ostgres=# SELECT DISTINCT (t.type) FROM (
             SELECT DISTINCT (o.type)
               FROM pg_depend d,
                    pg_identify_object_as_address(d.refclassid, d.refobjid, d.refobjsubid) o
                 UNION ALL
             SELECT DISTINCT (o.type)
               FROM pg_depend d,
                    pg_identify_object_as_address(d.classid, d.objid, d.objsubid) o
             WHERE d.classid != 0
           ) t;

           type            
---------------------------
 access method
 aggregate
 cast
 collation
 conversion
 domain constraint
 extension
 function
 function of access method
 index
 language
 materialized view
 operator
 operator class
 operator family
 operator of access method
 rule
 schema
 table
 table column
 text search configuration
 text search dictionary
 text search parser
 text search template
 toast table
 toast table column
 type
 view
 view column
(29 rows)

Change history

Examples

Basic usage of pg_get_object_address():

postgres=# SELECT * FROM pg_get_object_address('extension', '{plpgsql}', '{}');
 classid | objid | objsubid 
---------+-------+----------
    3079 | 14000 |        0
(1 row)

The inverse of the above using pg_identify_object_as_address():

postgres=# SELECT * FROM pg_identify_object_as_address(3079, 14000, 0);
   type    | object_names | object_args 
-----------+--------------+-------------
 extension | {plpgsql}    | {}
(1 row)

Identifying a table:

postgres=# SELECT * FROM pg_get_object_address('table', '{foo}', '{}');
 classid | objid | objsubid 
---------+-------+----------
    1259 | 16525 |        0
(1 row)

Identifying a table with explicit schema:

postgres=# SELECT * FROM pg_get_object_address('table', '{public,foo}', '{}');
 classid | objid | objsubid 
---------+-------+----------
    1259 | 16525 |        0
(1 row)

Identifying a table column:

postgres=# SELECT * FROM pg_get_object_address('table column', '{foo,id}','{}');
 classid | objid | objsubid 
---------+-------+----------
    1259 | 16525 |        1
(1 row)

Identifying a function:

postgres=# CREATE FUNCTION bar (int) RETURNS int LANGUAGE SQL AS 'SELECT 1';
CREATE FUNCTION

postgres=# SELECT * FROM pg_get_object_address('function', '{bar}', '{int}');
 classid | objid | objsubid 
---------+-------+----------
    1255 | 16528 |        0
(1 row)

Identifying an access method operator:

postgres=# SELECT * FROM pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{integer,integer}');
 classid | objid | objsubid 
---------+-------+----------
    2602 | 10148 |        0
(1 row)

Attempting to identify a non-existent object type:

postgres=# SELECT * FROM pg_get_object_address('foo', '{bar}', '{}');
ERROR:  unrecognized object type "foo"

Categories

System catalogue, System function

See also

pg_identify_object_as_address(), pg_depend