¿Cómo saber cuales son las tablas y su detalle por medio de una consulta?
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.