Wednesday, April 5, 2023

PostgreSQL - Find all object types in the schema

equivalent of oracle user_objects


select 
    nsp.nspname as SchemaName
    ,cls.relname as ObjectName 
    ,rol.rolname as ObjectOwner
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'TYPE'
        else cls.relkind::text
    end as ObjectType
from pg_class cls
join pg_roles rol 
on rol.oid = cls.relowner
join pg_namespace nsp 
on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
    and nsp.nspname not like 'pg_toast%'
    and rol.rolname = 'schema_name' and cls.relname='object_name' 
order by nsp.nspname, cls.relname

No comments:

Post a Comment

  PostgreSQL 16: Streaming Replication Assumptions: −      Same version of PostgreSQL is installed on the Primary and Standby DB servers. − ...