PG获取对象 oid
Tags： oidPGPostgreSQL
👉 本文共约200个字，系统预计阅读时间或需1分钟。
获取数据库的 oid
1 2 3 4 5 6 7 8 9 10 11 | -- 注意大小写敏感，要用小写 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 方法一：大小写敏感，要用小写 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- 方法一：大小写敏感，要用小写 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) |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
获取表，索引等对象的 oid
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- 方法一：大小写敏感，要用小写 ---查询表的 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 方法一：大小写敏感，要用小写 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) |
参考
https://blog.csdn.net/songyundong1993/article/details/121624720
