¿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.