Listar schemas

[huesos52]

Para ver los comentarios de los esquemas, encontré esta consulta:

Código SQL:

SELECT 
CASE 
    WHEN nspname LIKE E'pg\_temp\_%' THEN 1 
    WHEN (nspname LIKE E'pg\_%') THEN 0  
    ELSE 3 
END AS nsptyp, nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) 
    AS namespaceowner, 
    nspacl, description,  
    has_schema_privilege(nsp.oid, 'CREATE') AS cancreate 
FROM pg_namespace nsp 
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid  
WHERE NOT ((nspname = 'pg_catalog' AND EXISTS 
(SELECT 1 FROM pg_class 
    WHERE relname = 'pg_class' 
    AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'information_schema' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'tables' 
            AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname LIKE '_%' AND 
    EXISTS (SELECT 1 FROM pg_proc 
            WHERE proname='slonyversion' 
            AND pronamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'dbo' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'systables' 
            AND relnamespace = nsp.oid LIMIT 1)) OR  
(nspname = 'sys' AND 
    EXISTS (SELECT 1 FROM pg_class 
            WHERE relname = 'all_tables' 
            AND relnamespace = nsp.oid LIMIT 1))
) 
AND nspname NOT LIKE E'pg\_temp\_%'
AND nspname NOT LIKE E'pg\_toast_temp\_%' 
ORDER BY 1, nspname
 

Si te fijas, la información de los esquemas se almacena en una tabla llamada pg_namespace y los comentarios en su mayoria están en pg_description.

Ya es cuestion de empezar a entender a fondo la arquitectura que maneja postgresql para obtener este tipo de reportes.


Fuente: http://golden13.blogspot.com/2012/08...n-about_7.html