A primera mano creemos que lamentablemente PostgreSQL no tiene una sentencia de comandos para explorar la estructura interna de la Base de Datos, como el que nos liste los usuarios, base de datos, tablas, vistas, funciones, triggers etc…, como en el caso de Oracle y MySQL entre otros, resulta que si existe y no le falta nada, es una herramienta de la línea de comandos llamada psql, por lo que se requiere revisarla con detalle para conocer todo su poder, ya que ofrece mucho mas de lo que podemos pensar si es la primera vez que interactuamos con ella. De todos modos, al final muestro dos consultas que permite obtener información bastante amplia de las tablas y funciones, esta ultima con su respectivo DDL que también vale la pena revisar.

Conexión del cliente al Manejador de Base de Datos:

psql -h host -U usuario BaseDeDatos

Una vez dentro podemos hacer todas las operaciones sin limitación, a pesar de su sencillez oculta muchas funciones que pueden ser ejecutadas por una serie de comandos simples como por ejemplo:

Información básica:

  • \? Obtener ayuda, empiece siempre por aquí siempre para conocer mayor detalle.
  • \l Lista todas las Base de Datos
  • \dt Lista las tablas dentro de la Base de Datos que estamos conectados.
  • \dv Lista las vistas.
  • \dd Lista los comentarios de cualquier objeto.

Funciones:

  • \df Lista todas las funciones existentes.
  • \df+ nombre_funcion Lista información relacionada a la función y su ddl.
  • \ef nombre_funcion Edita la función especificada y solo existe partir de la version 8.4 de psql.

En la mayoría de los comandos mencionados, si le colocamos seguido un simbolo (+, mas), podremos obtener mayor información del objeto que estamos haciendo referencia:

dt+

También podemos especificar que objeto en especifico queremos el detalle.

dt+ tabla_ejemplo

De todos modos consulte la ayuda de la línea de comandos para obtener mayor información.

Recomiendo mucho el uso de la herramienta psql para el manejo de transacciones, por lo que me parece perfecta y entrega un total dominio al usuario, si aprendemos a usarla con todo su potencial, creará de ustedes un buen operador de PostgreSQL.

En caso de que por algún capricho necesitamos un poco más, aquí les dejo dos consultas que permiten listar las tablas con lujo de detalle y la que listas las funciones con sus respectivos ddl, cosa que puede ser muy útil en caso de editar dichas funciones en la Terminal si están por debajo de la versión 8.4.

La siguiente vista lista todas las tablas:

CREATE OR REPLACE VIEW tables AS 
 SELECT n.nspname AS schmema,
        c.relname AS name,
        a.attnum AS id,
        a.attname AS column_name,
        t.typname AS column_type, 
        CASE WHEN (a.atttypmod - 4) < 0 THEN 0
             ELSE a.atttypmod - 4
        END AS type_lenght, (SELECT COALESCE((SELECT d.adsrc
                                              FROM pg_attrdef d
                                              WHERE d.adrelid = c.oid
                                                AND d.adnum   = a.attnum), '') AS "coalesce") AS default_value,
        a.attnotnull AS not_null, 
        CASE WHEN ((SELECT count(DISTINCT sqa.attname) AS count
                    FROM pg_class sqc1, pg_attribute sqa
                    WHERE sqa.attrelid = sqc1.oid
                      AND sqa.attname  = a.attname
                      AND (sqc1.oid IN (SELECT sqi.indexrelid
                                        FROM pg_index sqi, pg_class sqc2, pg_namespace sqn
                                        WHERE sqc2.relname  ~~* c.relname
                                          AND sqc2.oid        = sqi.indrelid
                                          AND sqi.indisunique = true
                                          AND sqn.nspname   ~~* "current_schema"()::text)))) > 0 THEN true
            ELSE false
        END AS "unique",
        CASE WHEN ((SELECT count(DISTINCT sqa.attname) AS count
                    FROM pg_class sqc1, pg_attribute sqa
                    WHERE sqa.attrelid = sqc1.oid
                      AND sqa.attname = a.attname
                      AND (sqc1.oid IN (SELECT sqi.indexrelid
                                        FROM pg_index sqi, pg_class sqc2, pg_namespace sqn
                                        WHERE sqc2.relname   ~~* c.relname
                                          AND sqc2.oid         = sqi.indrelid
                                          AND sqi.indisprimary = true
                                          AND sqn.nspname    ~~* "current_schema"()::text)))) > 0 THEN true
            ELSE false
        END AS primary_key,
        (SELECT COALESCE((SELECT sqd.description
                          FROM pg_description sqd
                          WHERE sqd.objoid = a.attrelid
                            AND sqd.objsubid = a.attnum), ''::text) AS "coalesce") AS description,
        (SELECT COALESCE((SELECT sqcf.relname
                          FROM pg_attribute sqa
                          JOIN pg_class sqc ON sqc.oid = sqa.attrelid AND sqc.relkind = 'r'::"char"
                          JOIN pg_namespace sqn ON sqn.oid = sqc.relnamespace
                          JOIN pg_constraint sqct ON sqct.conrelid = sqa.attrelid AND sqct.confrelid <> 0::oid AND sqct.conkey[1] = sqa.attnum
                          JOIN pg_class sqcf ON sqcf.oid = sqct.confrelid AND sqcf.relkind = 'r'::"char"
                          JOIN pg_namespace sqnf ON sqnf.oid = sqcf.relnamespace
                          JOIN pg_attribute sqaf ON sqaf.attrelid = sqct.confrelid AND sqaf.attnum = sqct.confkey[1]
                          WHERE sqn.nspname ~~* "current_schema"()
                            AND sqc.relname ~~* c.relname
                            AND sqa.attname ~~* a.attname::text), ''::name) AS "coalesce") AS foreign_table,
        (SELECT COALESCE((SELECT sqaf.attname
                          FROM pg_attribute sqa
                          JOIN pg_class sqc ON sqc.oid = sqa.attrelid AND sqc.relkind = 'r'::"char"
                          JOIN pg_namespace sqn ON sqn.oid = sqc.relnamespace
                          JOIN pg_constraint sqct ON sqct.conrelid = sqa.attrelid AND sqct.confrelid <> 0::oid AND sqct.conkey[1] = sqa.attnum
                          JOIN pg_class sqcf ON sqcf.oid = sqct.confrelid AND sqcf.relkind = 'r'::"char"
                          JOIN pg_namespace sqnf ON sqnf.oid = sqcf.relnamespace
                          JOIN pg_attribute sqaf ON sqaf.attrelid = sqct.confrelid AND sqaf.attnum = sqct.confkey[1]
                          WHERE sqn.nspname ~~* "current_schema"()::text
                            AND sqc.relname ~~* c.relname::text
                            AND sqa.attname ~~* a.attname::text), ''::name) AS "coalesce") AS foreign_column
FROM pg_class c, pg_namespace n, pg_attribute a, pg_type t
WHERE c.relkind  = 'r'::"char"
  AND n.oid      = c.relnamespace
  AND a.attrelid = c.oid
  AND a.atttypid = t.oid
  AND a.attnum   > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

La siguiente vista lista todas las funciones:

CREATE VIEW functions AS
SELECT p.proname as name,
       'CREATE OR REPLACE '
       || p.proname
       || '('
       || CASE WHEN p.proretset THEN 'setof ' ELSE '' END
       || CASE WHEN proallargtypes IS NOT NULL
               THEN pg_catalog.array_to_string(ARRAY(
                   SELECT CASE WHEN p.proargmodes[s.i] = 'i' THEN '' 
                               WHEN p.proargmodes[s.i] = 'o' THEN 'OUT ' 
                               WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
                          END ||
                          CASE WHEN COALESCE(p.proargnames[s.i], '') = ''
                               THEN ''
                               ELSE p.proargnames[s.i] || ' ' 
                          END ||
                          pg_catalog.format_type(p.proallargtypes[s.i], NULL)
                   FROM pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)), ', ')
               ELSE pg_catalog.array_to_string(ARRAY(
                   SELECT CASE WHEN COALESCE(p.proargnames[s.i+1], '') = ''
                               THEN '' 
                               ELSE p.proargnames[s.i+1] || ' ' 
                          END ||
                          pg_catalog.format_type(p.proargtypes[s.i], NULL)
                   FROM pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i) ), ', ') 
               END
       ||')returns '
       ||pg_catalog.format_type(p.prorettype, NULL)
       ||' language '
       || l.lanname 
       ||' as $body$'
       ||p.prosrc
       ||'$body$' as ddl 
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
LEFT JOIN pg_catalog.pg_language  l ON l.oid = p.prolang 
JOIN pg_catalog.pg_roles          r ON r.oid = p.proowner 
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype 
  AND (p.proargtypes[0] IS NULL 
   OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype) 
  AND NOT p.proisagg 
  AND pg_catalog.pg_function_is_visible(p.oid)

Espero que todo esto les ayude mucho en las actividades administrativas con PostgreSQL.