Descubriendo la metadata de una tabla (postgreSQL)

Mientras desarrollaba una idea de un ORM básico para PHP que auto-descubriera la metadata de una tabla (tal y como se hacen en mySQL con “DESCRIBE”), me encuentro con el problema de que en postgreSQL no existe nada parecido a DESCRIBE o a SHOW CREATE de mySQL; por ende, nos toca interrogar a los pg_catalogs para obtener la información de metadatos de la tabla.

En este caso, queriamos obtener una estructura semejante a esta:

column_name => cedula, datatype=>integer, key=>PRI, is_nullable=>NO, max_length=>8, column_default =>

Donde:

datatype: es un tipo de datos interno de postgresql

key: UNI = Unique, el campo es un indice unico, PRI = Primary, el campo es un indice primario, FK = Foreign key, el campo es un indice de una clave foránea.

max_length: Si el campo es integer, muestra la precisión del entero (2,4,8), si es un varchar, la longitud en caracteres (ej. 75)

column_default: muestra el tipo de valor por defecto de la tabla; si la tabla es serial, veremos la llamada al nextval de la secuencia:

ej. nexval(‘personas_cliente_id_seq’::regclass)

Lo que nos permite determinar que campo de nuestra tabla es serial (auto-incremental).

Entendiendo los esquemas: 

Para entender; cada tabla del pg_catalog debe ser interrogada con el oid de la tabla, que lo sacamos de pg_class.

Los campos y sus atributos, los sacamos de la tabla pg_attribute.

El tipo de datos lo sacamos de la tabla pg_type

los constraints de la tabla los obtenemos de la tabla pg_constraint

y el valor por defecto, lo sacamos de la tabla pg_attrdef.

La sentencia construida para sacar esa información de una sola vez de todas las tablas es esta:

SELECT a.attname as column_name, t.typname as data_type,
CASE
WHEN cc.contype=’p’ THEN ‘PRI’
WHEN cc.contype=’u’ THEN ‘UNI’
WHEN cc.contype=’f’ THEN ‘FK’
ELSE ” END AS key,
CASE WHEN a.attnotnull=false THEN ‘YES’ ELSE ‘NO’ END AS is_nullable,
CASE WHEN a.attlen=’-1′ THEN (a.atttypmod – 4) ELSE a.attlen END as max_length,
d.adsrc as column_default
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
LEFT JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
LEFT JOIN pg_catalog.pg_constraint cc ON cc.conrelid = c.oid AND cc.conkey[1] = a.attnum
LEFT JOIN pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND a.attnum = d.adnum
WHERE c.relname = ‘<TABLA>’ AND a.attnum > 0 AND t.oid = a.atttypid

donde <TABLA> representa el nombre de la tabla a la que queremos interrogar para obtener sus metadatos.

Espero que les sirva; en la siguiente entrega, descubrir los metadatos de una tabla de mySQL y de una entrada de LDAP.

Acerca de phenobarbital

http://about.me/phenobarbital

Publicado el 25 febrero 2008 en Databases, Linux, PHP, PlanetaLinux. Añade a favoritos el enlace permanente. 19 comentarios.

  1. Loco prende ese telefono!!! llevo 2 semanas intentando hablar contigo.

  2. Mmm, estas seguro que ninguno de los drivers/librerias de php para pgsql implementa esto ya? Suena como fastidioso hacer esto, ya que es posible que lo necesites siempre.

    Me refiero, ya lo he hecho, hace ya bastantes años, con Java y JDBC, y esta tarea está definida en las API estandar, e implementada por los drivers especificos de pgsql, mysql, y un largo etc.

    Un caso practico es algo asi como:

    ResultSet rs = stmt.executeQuery(“SELECT campo, campo2, campo3 FROM mitabla”);
    ResultSetMetaData rsmd = rs.getMetaData();

    Y obtengo la data con getColumnName(1), getColumnType(1), getColumnTypeName(1), getPrecision(1) , etc etc etc.

    Y no tienes que reimplementar el codigo para cada DBMS , suena mucho mas consistente🙂

    Saludos.

    http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSetMetaData.html

  3. saludos, jesus queria hacerte una pregunta en cuanto a tu framework tomates que ha pasado con este proyecto? tienes algún svn? etc una vez me lo dieron para probarlo y me gusto bastante la forma como se manejan muchas cosas y me gustaria ensuciarme mas las manos con este framework y bueno de alguna manera ayudar en lo que se pueda…

    Éxitos…

  4. Buenas compa, espero que no te moleste que te pida un consejo sobre este tema, yo en este momento estoy muy interesado en aprender sobre diseño y mantenimiento de base de datos en Linux y me interesa principalmente dos aplicaciones, una es Gambas y la otra PostgreSQL pero me toca aprender por mi cuenta porque no he encontrado ninguna institución que brinde cursos sobre este tema y dado que me encontré con este articulo quisiera aprovechar para pedirte algún consejo sobre la forma más eficiente de aprender a trabajar con PostgreSQL por mi cuenta. Cualquier consejo sobre este tema es bienvenido y si te parece que mi petición está de más lo entenderé.

    saludos…

  5. Fenix; estoy en proceso de transferirte tu dominio; en estos momentos con los peos de dólares me es dificil mantener un ancho de banda abultado y otras cosas en el server; además lo tengo dedicado a más de 10 clientes en este momento (+trabajo, +viajes, +etc, +etc); dame chance que esté un poco más desocupado y hablamos si?…
    Mil disculpas …

  6. Gerardo; ventajas de los que tienen un namespace!; en el caso de PHP no existe; por lo que cada “extensión” de conector de datos (pgsql, oci, mysql, mysqli, etc) tiene una forma COMPLETAMENTE DISTINTA de extraer los metadatos; de esta manera, hay una unica forma probable (y para mí más limpia y compatible entre todas las DB) que es interrogar directamente al information_schema (en el caso de LDAP, al schema respectivo) e iterar un único resultset; en mi último test de velocidad con un debug profiler; consultar el information_schema es mucho más rápido que consultar + luego pedir los metadatos (aunque en la extensión mysqli los metadatos si se vienen junto con la consulta); ¿a que se debe esta “distrofia” en las extensiones?; no sé; es de esas incongruencias de PHP que a veces fastidian; aunque si te soy sincero, prefiero consultar para luego interar un resultset; que hacer llamadas a x cantidad de funciones para cada una de las cosas que requiero conocer de los metadatos de una tabla.
    Ya entiendes por qué la gente prefiere hacer un mapa de la tabla en XML o en YAML en vez de pegarse en vivo en la DB?; amerita conocer la estructura DDL de todas las DB a las que les quieras hacer un conector ORM.

  7. Hola Cachi; el proyecto tiene SVN más aún no tiene página (mis labores no me permiten dedicarle todo el tiempo que quisiera); el repo es http://svn.covetel.com.ve/tomates/trunk/; el SVN permite checkout anónimo así que os puedes descargar sin problemas.
    Claro que puedes participar!; si deseas ensuciarte las manos con código; puedo darte posibilidades de commit al SVN y cosas así; hacerte llegar la documentación (que aun es escasa :p) y en lo que puedas colaborar estoy pendiente! … pronto haré un artículo y colocaré una página oficial.

  8. La forma más práctica yo la dividiría en 3 ramas (ninguna te costará mucho); la primera es instalar un postgreSQL 8 en tu máquina y descargar proyectos que usen postgreSQL como backend para que entiendas algo de como se construyen databases, schemas, tables, constraints, etc.
    La segunda forma es el PDF oficial; tiene muy buena información (creo que ya está portado al español).
    La tercera forma (super-practica) es que crees una DB demo y comiences a jugar con sus opciones; toma bases de datos mySQL y usa un ejemplo como (http://www.jesuslara.com.ve/blog/2007/05/13/experimento-con-datos-migrando-a-postgresql-tercera-parte/) para migrar datos a postgreSQL.
    La otra cosa que debes conocer es ANSI SQL; las bases de datos en SL son muy respetuosas del ANSI SQL 99/2003; así que no tendrás muchos problemas en entender la forma de gestión.
    En cuanto a gambas, tengo entendido que es un Basic más orientado a objetos que VB y con soporte a formularios via GTK; no he visto mucho por ahi ni ejemplos ni nada; si logras algo avisas! …
    Suerte!

  9. Se me olvidaba Fral la cuarta forma no comentada (es obvia así que se me olvidó ponerla! jajaja), pregunta!, estamos a la orden …

  10. Ah bueno excelente si puedes hacerme llegar la Documentación así sea escasa no importa y bueno la idea es ayudar en lo que se pueda.
    deivinsontejeda (at) gmail.com

  11. Muchas gracias por tu respuesta amigo y tendré muy en cuenta tus consejos, ya creo tener suficiente material para empezar el proceso así que a ver a como nos toca jeje.

    Gracias de nuevo y que estés bien.

    saludos…

  12. Hola como estas? debo hacer una conexion a una o varias bases de datos.. estoy desarrollando bajo kumbia php apache msql.. es decir con una LAMP.. necesito q a traves de unos parametros de conexion que ya tengo solicitados pueda saber q bases de datos le pertenece o q coincida con esos parametros.. y poder llenar un combo con esas bases de datos y a sus llenar otro con los tablas.. Cualquier ayuda sera valorada.. Gracias de antemano

  13. Hola Veronica; no discutiré si usas kumbia (en mi gusto, prefiero codeIgniter o en su defecto Prado) pero obtener la metadata (que es lo que tu necesitas) para mySQL es bastante sencillo; simplemente ejecuta:
    SHOW DATABASES;
    Para obtener una lista de todas las bases de datos que el usuario con el que te estás conectando tiene derecho a ver; posteriormente ejecutas:
    SHOW TABLES;
    para ver las tablas que posee el sistema
    Como ultimo al seleccionar una tabla; puedes ejecutar:
    SHOW COLUMNS FROM nombredetabla
    o un DESCRIBE nombredetabla
    Para obtener la metadata de la tabla; es esto lo que necesitas?

  14. Hola chino! Soy nueva con PostgreSQL, y mi pregunta es la siguiente… Tengo una base de datos que tiene aproximadamente 727 tablas, y me piden una aplicación para generar reportes… Según lo que he empezado a investigar, aplicaciones como esa ya existen en el mercado, y bueno supongo que bastará guiarme por una de ellas, para poder trabajar… Mi dilema está en que me piden que debo llevar esas tablas a su mínima expresión para poder trabajar en postgresql junto php para generar los reportes… Como haría para poder reducir el número de tablas?? dado que en el reporte debo mostrar, las relaciones que existen entre varias tablas…
    Gracias..

  15. Hola!, novata en este mundo de postgre y PHP. Pasa lo siguiente: Debo realizar un generador de reportes que me permita de una tabla detalle cualquiera, mostrar la o las maestro que están relacionadas a ellas, y a su vez, poder escoger uno o varios campos de una de esas tablas maestro. El código que tienes por allí funciona para generarme las tablas detalles, pero realmente desconozco como hacer para generar las maestro a partir de la detalle seleccionada.. La base de datos con la cual estoy trabajando posee 727 tablas y la idea principal es que pueda a partir de este código minimizar la cantidad de tablas, pero también poder utilizar cualquier campo de las tablas maestro cuando sea requerido… Crees tener algo que pueda servirme??? Te lo agradecería enormemente…..

    P.D. Phenobarbital con soda…?? Buen seudónimo para tu blog!!

  16. Hola! aqui un nuevo usuario postgre luchando con la adaptacion y los fantasmas de oracle, se me ha hecho bastante complicado o quizas fastidioso esa migracion y lo mas probable es por la oposicion al cambio, pues indiferentemente
    en estos momentos quisiera entender y aprender mas a este manejador, con el que soy un novato que por ahora se
    ha frustrado pero es parte del proceso, ahora para puntualizar, en estos momentos tengo problemas con la ubicacion
    de todos los objetos de la BD (Tables,Views, Functios, Domains,Indexes,Sequences) es decir todos, y cada uno de estos llevarlos a un Tablespace propio, asi de esta manera manejar cada objeto con su tablespace. En fin ya logre hacer este moviento bajo un scrip donde logro amarrar los indices a traves de la metadata la “pg_indexes” y el “pg_tables” donde amarro los indices con las tablas y de esta forma logro enviarlos a su tablespace.

    aqui les adjunto el scrip para que tengan una idea y ver si me pueden ayudar a hacer lo mismo con el resto de los objetos

    select ‘ALTER INDEX ‘ || i.indexname || ‘ SET TABLESPACE Index;’
    from pg_indexes i,pg_tables t
    where i.tablename = t.tablename
    and t.tableowner=’postgres’

    pd: estoy con el desarrollo analogo para el pg_view, pg_tables, tengo problemas como amarrar el resto (Functios, Domains,Sequences.)

    Me despido y de antemano gracias por la ayuda.

    • para eso simplemente puedes usar INFORMATION_SCHEMA, PostgreSQL sigue el estandar SQL92 con el cual puedes consultar las tablas, procedimientos, etc de la BD

      • Hola lzero, cuando se escribió este artículo (2007) postgreSQL aún no habia implementado el information_schema, que entró en vigor en postgreSQL 8 a finales del 2008
        Gracias por el comentario!😉

  17. Hola, por favor necesito ayuda es esto, como se realiza a partir de una instrucción SQL (postgresql), que la salida sea en XML

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: