It can be used as a translator when querying the dictionary tables in postgres.
For example, the following query produces an error, since the predicate expects an integer ("oid"):
select relname,relowner,reltuples,relkind from pg_class where oid='myuser.mytable_1'; ERROR: invalid input syntax for type oid: "myuser.mytable_1" LINE 1: ...lowner,reltuples,relkind from pg_class where oid='myuser.mytabl...
However, if you add "::regclass", the alias for the object is used instead of the literal string:
select relname,relowner,reltuples,relkind from pg_class where oid='myuser.mytable_1'::regclass;
relname | relowner | reltuples | relkind
-----------+----------+-----------+---------
mytable_1 | 69094 | 0 | p
Note that there are several types of OIDs. For example, for roles (users), you should use "regrole" instead of "regclass":
select relname,relowner,reltuples,relkind from pg_class where relowner='myuser'::regrole;
relname | relowner | reltuples | relkind
-----------------------+----------+-----------+---------
unique_idx_2 | 69094 | 0 | I
pg_toast_191274 | 69094 | 0 | t
mytable_1_id_seq | 69094 | 1 | S
mytable_1_pkey | 69094 | 0 | I
unique_idx_1 | 69094 | 0 | i
mytable_2 | 69094 | 0 | p
mytable_3 | 69094 | 0 | r
If you use them together, translate to different types of OID. In the below case, it's really not necessary, but illustrates the point nevertheless:
select relname,relowner,reltuples,relkind from pg_class where oid='myuser.mytable_1'::regclass and relowner='myuser'::regrole;More information about OIDs
More information about pg_class
No comments:
Post a Comment