内容

pg_get_object_address()

返回指定对象 OID 的函数

pg_get_object_address() 是一个系统函数，用于返回提供的类型代码、对象名称和（可选）参数数组的对象标识符。

pg_get_object_address() 在 PostgreSQL 9.5 中添加。

用法

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

返回的值对应于 pg_depend 中使用的标识列；要执行反向操作，即返回对象的类型等的名称，请参见 pg_identify_object_as_address()。

type 的有效值没有明确记录，但可以通过以下查询推断

postgres=# 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)

更改历史记录

示例

pg_get_object_address() 的基本用法

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

使用 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)

识别表

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

使用显式模式识别表

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

识别表列

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

识别函数

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)

识别访问方法运算符

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)

尝试识别不存在的对象类型

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

分类

系统目录，系统函数

另请参阅

pg_identify_object_as_address()，pg_depend

反馈

提交您对 "pg_get_object_address()" 的任何评论、建议或更正 此处。