Postgresql: Looking at Postgresql view the Oracle's way.

I am so not used to all these postgresql OIDs. Though the IDs are necessary to map to physical files at the filesystem layer, I do not see a reason why not making them more intuitive to look at. I will continue to build this to my liking. 

VCDB=#

VCDB=# SELECT pg_catalog.pg_relation_filenode(c.oid) as "Object ID", relname as "Object Name",

VCDB-# case WHEN relkind='r' THEN 'Table'

VCDB-#      when relkind='m' THEN 'Materialized View'

VCDB-#  when relkind='i' THEN 'Index'

VCDB-#  when relkind='S' THEN 'Sequence'

VCDB-#  when relkind='t' THEN 'Toast'

VCDB-#      when relkind='v' THEN 'View'

VCDB-#  when relkind='c' THEN 'Composite'

VCDB-#  when relkind='f' THEN 'Foreign_Table'

VCDB-# ELSE 'other'

VCDB-#     end

VCDB-#  as "Object Type", o.rolname as "Owner"

VCDB-# FROM pg_catalog.pg_class c

VCDB-#         LEFT JOIN pg_catalog.pg_authid o ON o.oid=c.relowner

VCDB-#         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

VCDB-#         LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),

VCDB-#         pg_catalog.pg_tablespace t

VCDB-# WHERE

VCDB-#   t.oid = CASE

VCDB-#                         WHEN reltablespace <> 0 THEN reltablespace

VCDB-#                         ELSE dattablespace

VCDB-#                 END

VCDB-# ;

Object ID |                Object Name                 | Object Type |  Owner

-----------+--------------------------------------------+-------------+----------

     12362 | pg_statistic                               | Table       | postgres

     12368 | pg_type                                    | Table       | postgres

     16397 | cis_kv_providers_surr_key_seq              | Sequence    | vc

     16403 | pg_toast_16399                             | Toast       | vc

     16405 | pg_toast_16399_index                       | Index       | vc

     16399 | cis_kv_providers                           | Table       | vc

     12364 | pg_toast_2619                              | Toast       | postgres

     12366 | pg_toast_2619_index                        | Index       | postgres

     12374 | pg_authid_rolname_index                    | Index       | postgres

     12375 | pg_authid_oid_index                        | Index       | postgres

     12382 | pg_attribute_relid_attnam_index            | Index       | postgres

     12383 | pg_attribute_relid_attnum_index            | Index       | postgres

     12386 | pg_toast_1255                              | Toast       | postgres

     12388 | pg_toast_1255_index                        | Index       | postgres

Oracle’s

SYS>  select object_id, object_name, object_type, owner from dba_objects where rownum < 12 and owner='VC51';

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE         OWNER

---------- ------------------------------ ------------------- ------------------------------

     81387 CALC_TOPN1_PROC                PROCEDURE           VC51

     81388 CALC_TOPN2_PROC                PROCEDURE           VC51

     81389 CALC_TOPN3_PROC                PROCEDURE           VC51

     81390 CALC_TOPN4_PROC                PROCEDURE           VC51

     81371 CLEANUP_EVENTS_TASKS_PROC      PROCEDURE           VC51

     81391 CLEAR_TOPN1_PROC               PROCEDURE           VC51

     81392 CLEAR_TOPN2_PROC               PROCEDURE           VC51

     81393 CLEAR_TOPN3_PROC               PROCEDURE           VC51

     81394 CLEAR_TOPN4_PROC               PROCEDURE           VC51

     81368 DELETE_STATS_PROC              PROCEDURE           VC51

     80730 IDX_VPX_HIST_STAT1_100_TID     INDEX               VC51

11 rows selected.

SYS>