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

Oracle row type vs PostgreSQL record

The Oracle row type concept can be accomplished with the record type in PostgreSQL. Example: Oracle: CURSOR travel_cur IS  SELECT  c.travel_...