Friday, September 5, 2025

Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL

SELECT schema_name, object_name, object_type
FROM (
    -- Tables, Views, Indexes, etc.
    SELECT
        n.nspname AS schema_name,
        c.relname AS object_name,
        CASE c.relkind
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'VIEW'
            WHEN 'm' THEN 'MATERIALIZED VIEW'
            WHEN 'i' THEN 'INDEX'
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'f' THEN 'FOREIGN TABLE'
            WHEN 'p' THEN 'PARTITIONED TABLE'
            WHEN 'I' THEN 'PARTITIONED INDEX'
            ELSE c.relkind::text
        END AS object_type
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = current_schema()

    UNION ALL

    -- Functions
    SELECT
        n.nspname AS schema_name,
        p.proname AS object_name,
        'FUNCTION' AS object_type
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = current_schema()
) AS objects
ORDER BY object_type, object_name;
It only shows objects in the current schema (like Oracle USER_OBJECTS).

If you want all objects the user owns (across schemas), replace
WHERE n.nspname = current_schema()
with
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')

No comments:

Post a Comment