FAQ - PostgreSQL

[gnzsoloyo]

Ver los comentarios de los schemas
Aporte de @huesos52.

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