[Pgsql-ayuda] =?ISO-8859-1?Q?C=F3mo?= obtener las tablas de una base de datos

[Gunnar Wolf]

> Pues eso. C=F3mo puedo obtener las tablas que tiene una base de datos (si=
n
> utilizar \l, quiero hacerlo con SQL :) y .. adem=E1s, una vez conectado a
> una tabla obtener los campos y caracter=EDsticas de los campos de la tabl=
a.
> Muchas gracias

Eso depende de tu motor de bases de datos, no es parte del est=E1ndar SQL.
Ahora, si lo quieres hacer espec=EDficamente en Postgres, te sugiero
asomarte a las fuentes para investigar c=F3mo es que lo hace el psql, que
es, a fin de cuentas, un cliente m=E1s de Postgres.

Postgres maneja toda la informaci=F3n relativa al sistema en metatablas,
tablas ocultas al usuario en general, pero que est=E1n disponibles al igual
que cualquier otra tabla.

Encontr=E9 (creo) lo que buscas en el archivo src/bin/psql/describe.c, a
partir de la l=EDnea 983 (de los fuentes de Postgres 7.0.3):

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
strcat(buf, "SELECT c.relname as \"Name\", 'table'::text as \"Type\", u.use=
name as \"Owner\"");
if (desc)
        strcat(buf, ", obj_description(c.oid) as \"Description\"");
strcat(buf, "\nFROM pg_class c, pg_user u\n"
           "WHERE c.relowner =3D u.usesysid AND c.relkind =3D 'r'\n"
           "  AND not exists (select 1 from pg_views where viewname =3D c.r=
elname)\n");
strcat(buf, showSystem ? "  AND c.relname ~ '^pg_'\n" : "  AND c.relname !~=
 '^pg_'\n");
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Ahora, para hac=E9rtelo un poco m=E1s f=E1cil - Te paso uno de los CGIs que
hicimos Roberto Andrade y yo trabajando para =D3pticas Lux.
Desafortunadamente perd=ED uno de los tres CGIs y el programa ya no est=E1
completo (a menos que Roberto lo tenga)... Pero bueno, este te mostrar=E1
perfectamente lo que necesitas. Claro est=E1, este c=F3digo es GPL, as=ED q=
ue
=FAsalo para lo que quieras.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
#!/usr/bin/perl
use DBI;
use CGI qw/:standard/;


my ($dbname, $dbh, $sth, $sql, @row, @tablas);

$dbname=3D 'base_de_datos;
$dbh =3D DBI->connect("dbi:Pg:dbname=3D$dbname");
$dbh->do("SET DateStyle TO 'European'");

print header, start_html(-title=3D>"Lista de tablas",
                         -bgcolor=3D>"White"),
    "<FONT FACE=3D'Arial,Helvetica'><CENTER>";

# Obtenemos los nombres de la tablas
$sql =3D "select relname FROM pg_class, pg_user WHERE usesysid =3D relowner=
 and ( relkind =3D 'r') and relname !~ '^pg_'";
$sth =3D $dbh->prepare($sql);
$sth->execute;
# Los guardamos en @tablas
while (@row =3D $sth->fetchrow_array) {
        push(@tablas, $row[0]);
}
# Nos desconectamos
$sth->finish;

$dbh->disconnect;

print "<FONT FACE=3D'Arial,Helvetica'><CENTER>",
    h3("Seleccione una tabla para capturar"),
    start_form(-action=3D>"captura_tabla.pl"),
    popup_menu(-name=3D>"tabla",
               -values=3D>[@tablas],
               -default=3D>$tablas[0]),
    p,
    submit(-name=3D>"captura",
           -value=3D>"Capturar")," ",
    submit(-name=3D>"consulta",
           -value=3D>"Consultar");
print end_form, end_html;

# Nos desconectamos
$sth->finish;

$dbh->disconnect;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


------------------------------------------------------------
Gunnar Wolf - gwolf@campus.iztacala.unam.mx - (+52)5623-1118
Desarrollo y Admon. de Sistemas en Red - FES Iztacala - UNAM
Departamento de Seguridad en Computo   -   DGSCA    -   UNAM
------------------------------------------------------------
Quidquid latine dictum sit, altum viditur.