这是我参与11月更文挑战的第7天,活动详情查看:2021最后一次更文挑战
在使用 PostgreSql 数据库时,发现很多时候需要查询对象的 oid, 本文记录下查询对象的 oid 的方法,后续发现更方便的方式会再更新……
获取数据库的 oid
--注意大小写敏感,要用小写
chis=# select oid,datname from pg_database where datname='syd';
oid | datname
-------+---------
41351 | syd
(1 row)
chis=# select oid,datname from pg_database where datname='SYD';
oid | datname
-----+---------
(0 rows)
复制代码
获取用户或角色的 oid
--方法一:大小写敏感,要用小写
chis=# select oid,rolname from pg_authid where rolname='syd';
oid | rolname
-------+---------
66934 | syd
(1 row)
chis=# select oid,rolname from pg_authid where rolname='SYD';
oid | rolname
-----+---------
(0 rows)
--方法二:大小写不敏感,用大小写均可
chis=# select 'syd'::regrole::oid;
oid
-------
66934
(1 row)
chis=# select 'SYD'::regrole::oid;
oid
-------
66934
(1 row)
复制代码
获取 schema 的 oid
--方法一:大小写敏感,要用小写
chis=# select oid,nspname from pg_namespace where nspname='comm';
oid | nspname
-------+---------
16398 | comm
(1 row)
chis=# select oid,nspname from pg_namespace where nspname='COMM';
oid | nspname
-----+---------
(0 rows)
--方法二:大小写不敏感,用大小写均可
chis=# select 'comm'::regnamespace::oid;
oid
-------
16398
(1 row)
chis=# select 'COMM'::regnamespace::oid;
oid
-------
16398
(1 row)
复制代码
获取表,索引等对象的 oid
--方法一:大小写敏感,要用小写
---查询表的 oid
chis=# select oid,relname from pg_class where relname='account';
oid | relname
-------+---------
66640 | account
(1 row)
chis=# select oid,relname from pg_class where relname='ACCOUNT';
oid | relname
-----+---------
(0 rows)
--查询索引的 oid
chis=# select oid,relname from pg_class where relname='pk_account_id';
oid | relname
-------+---------------
66646 | pk_account_id
(1 row)
chis=# select oid,relname from pg_class where relname='PK_ACCOUNT_ID';
oid | relname
-----+---------
(0 rows)
--方法二:大小写不敏感,用大小写均可(注意需要指定schema名,不然表在当前schema不可见时,会报错)
---查询表的 oid
chis=# select 'account'::regclass::oid;
ERROR: relation "account" does not exist
LINE 1: select 'account'::regclass::oid;
^
chis=# select 'comm.account'::regclass::oid;
oid
-------
66640
(1 row)
chis=# select 'COMM.ACCOUNT'::regclass::oid;
oid
-------
66640
(1 row)
---查询索引的 oid
chis=# select 'pk_account_id'::regclass::oid;
ERROR: relation "pk_account_id" does not exist
LINE 1: select 'pk_account_id'::regclass::oid;
^
chis=# select 'comm.pk_account_id'::regclass::oid;
oid
-------
66646
(1 row)
chis=# select 'COMM.PK_ACCOUNT_ID'::regclass::oid;
oid
-------
66646
(1 row)
复制代码
获取函数的 oid
--方法一:大小写敏感,要用小写
chis=# select oid,proname from pg_proc where proname='out_tally_pepole_count';
oid | proname
-------+------------------------
16464 | out_tally_pepole_count
(1 row)
chis=# select oid,proname from pg_proc where proname='OUT_TALLY_PEPOLE_COUNT';
oid | proname
-----+---------
(0 rows)
--方法二:大小写不敏感,用大小写均可(注意需要指定schema名,不然表在当前schema不可见时,会报错)
chis=# select 'out_tally_pepole_count'::regproc::oid;
ERROR: function "out_tally_pepole_count" does not exist
LINE 1: select 'out_tally_pepole_count'::regproc::oid;
^
chis=# select 'finance.out_tally_pepole_count'::regproc::oid;
oid
-------
16464
(1 row)
chis=# select 'FINANCE.OUT_TALLY_PEPOLE_COUNT'::regproc::oid;
oid
-------
16464
(1 row)
复制代码
近期评论