postgresql的oid
- oid属性是被隐藏起来了,如通过pg_database,pg_namespace,pg_class表结构查看列信息是不可见的
- oid是唯一标识
1.获取数据库oid
select oid,* from pg_database;
select datname,oid from pg_database;
2.获取模式oid
select oid,* from pg_namespace;
select nspname,oid from pg_namespace where nspname in ('public',test2);
nspname|oid
-------|-----
public | 2200
test2 |16387
-- 利用regnamespace
select 'test2'::regnamespace::oid;
oid
-----
16387
3.获取表或索引oid
- pg_class表中的relname属性表示表名或者索引名称
-- 查看表的oid
select relname,oid from pg_class where relname in ('aa','bb');
relname|oid
-------|-----
bb |16391
aa |16384
aa |16388
-- 利用regclass查询oid
select 'aa'::regclass::oid;
oid
-----
16384
select 'test2.aa'::regclass::oid;
oid
-----
16388
-- 查看索引的oid
select relname,oid from pg_class where relname in ('aa_key');
relname|oid
-------|-----
aa_key |16397
4.获取用户或角色oid
-- 通过表pg_authid
select rolname,oid from pg_authid where rolname in ('zhangsan','postgres');
rolname |oid
--------|-----
postgres| 10
zhangsan|16398
-- 通过regrole,使用该方式即便没有pg_authid表的权限一样可以获取oid
-- SQL 错误 [42501]: ERROR: permission denied for table pg_authid
select 'zhangsan'::regrole::oid;
oid
-----
16398
5.获取函数oid
select oid,proname from pg_proc
-- 通过regproc
select 'proc_name'::regproc::oid;