Re: obtener info de tabla: campos, restricciones

Gracias por la sugerencia Gustavo. Estuve analizando las tablas de
pg_catalog y vi que 3 tablas tienen la informacion que busco:

********************
tabla: pg_constraint
--------------------
oid conname contype conrelid conkey[] confkey[]
----------------------------------------------------------
TABLA_pkey p o f OIDTABLA posCAMPO posCAMPO
TABLA_fk

***************
tabla: pg_class
---------------
oid relname
----------------
OID TABLA

*******************
tabla: pg_attribute
-------------------
attrelid attname attnum
-------------------------------
OIDTABLA CAMPO posCAMPO

Y programe las dos funciones que estan a continuación. La que me
devuelve el OID de la tabla funciona bien, la otra me falta afinar
para que devuelva todas las claves foraneas, que por ahora voy
concatenando los nombres en una cadena.
Pero tengo una duda, ¿se puede lograr lo mismo solo con SQL?, y si es
asi cual de los metodos es mejor, pasa que todavia no soy muy practico
con SQL.

*******************************************************************
-- Function: "f_TablaClavesPrimyAjenas"("Tabla" character varying)

-- DROP FUNCTION "f_TablaClavesPrimyAjenas"("Tabla" character varying);

CREATE OR REPLACE FUNCTION "f_TablaClavesPrimyAjenas"("Tabla" character varying)
RETURNS character varying AS
$BODY$
/* Funcion que devuelve Nombre de los campos que son
claves Primaria y Ajenas */
DECLARE
Tabla alias for $1;
oTabla oid;
sSQL varchar;
aRest smallint[];
aux integer;
sCampo varchar;
sClaves varchar;
fAjenas pg_catalog.pg_constraint%rowtype;
nCF integer;

begin /* oid, conname, contype, conrelid, conkey, confkey */
/* Buscar OID de Tabla en pg_class */
oTabla = public."f_Tablaoid"(Tabla);

/* Buscar Clave Primaria (Puede ser 1 Campo o Varios) en pg_constraint */
sSQL = 'select conkey'
|| ' from pg_constraint'
|| ' where conrelid = ' || oTabla
|| ' and contype = ' || quote_literal('p');
raise notice 'Primarias:%',sSQL;
execute sSQL into aRest;
sClaves = 'P';
/* aRest Tiene el/los attnum identificatorio/s del/os campo/s */
for i in array_lower(aRest,1)..array_upper(aRest,1) loop
/* Obtener nombre de pg_attribute */
sSQL = 'Select attname from pg_attribute'
|| ' where attrelid = ' || oTabla || ' and attnum = ' || aRest[i];
raise notice 'Buscar Campo P:%',sSQL;
execute sSQL into sCampo;
sClaves = sClaves || ' ' || sCampo;
raise notice 'Campo P:%',sCampo;
end loop;

/* Buscar Claves Foraneas o Ajenas (1 o mas) en pg_constraint */
sSQL = 'select *'
|| ' from pg_constraint'
|| ' where conrelid = ' || oTabla
|| ' and contype = ' || quote_literal('f');
raise notice 'Ajenas: %',sSQL;

sClaves = sClaves || ' F';
for fAjenas in execute sSQL loop

/*get diagnostics nCF = ROW_COUNT;
raise notice 'Cantidad Ajenas: %',nCF;
if nCF > 0 then*/

/* fAjenas Tiene filas con el/los attnum identificatorio/s
del/os campo/s */
for i in array_lower(fAjenas.confkey,1)..array_upper(fAjenas.confkey,1)
loop
/* Obtener nombre de pg_attribute */
sSQL = 'Select attname from pg_attribute'
|| ' where attrelid = ' || oTabla || ' and attnum = ' ||
fAjenas.confkey[i];
raise notice 'Buscar Campo F:%',sSQL;
execute sSQL into sCampo;
sClaves = sClaves || ' ' || sCampo;
raise notice 'Campo F:%',sCampo;
end loop;
end loop;
return sClaves;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

*****************************************************************************

-- Function: "f_Tablaoid"("Tabla" character varying)

-- DROP FUNCTION "f_Tablaoid"("Tabla" character varying);

CREATE OR REPLACE FUNCTION "f_Tablaoid"("Tabla" character varying)
RETURNS oid AS
$BODY$
/* Funcion que devuelve el oid de una Tabla */
DECLARE
Tabla alias for $1;
oTabla oid;
sSQL varchar;
begin
sSQL = 'select oid from pg_class where relname=''' || Tabla || '''';
execute sSQL
into oTabla;
/* raise notice 'SQL:%',sSQL; */
return oTabla;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

El 9/04/07, Gustavo <gustavor(at)intercomgi(dot)net> escribió:
> Probablemente soluciones tu problema modificando tu consulta de la siguiente
> manera
>
> DECLARE
> Tabla alias for $1;
> aux oid;
> begin
> select oid into aux
> from pg_class
> where relname=Tabla;
> return aux;
> end;
>
> Suerte,
>
> ----- Original Message -----
> From: "Blas Lopez" <blaslopez(at)gmail(dot)com>
> To: <pgsql-es-ayuda(at)postgresql(dot)org>
> Sent: Thursday, April 05, 2007 3:12 PM
> Subject: Re: [pgsql-es-ayuda] obtener info de tabla: campos, restricciones
>
>
> Utilizo pgAdmin III y Postgresql manager lite 3 para administrar el
> servidor.
> Analizando un poco el esquema vi que puedo sacar informacion de dos
> tablas pg_class y pg_constraint donde aparecen las tablas con sus oid
> y las claves primarias y foraneas tambien con sus oid.
> Por eso se me ocurrio escribir una funcion que reciba el nombre de la
> tabla y trate de devolver el/los campo/s que figura/n como clave
> primaria y/o el/los campo/s que figuran como clave/s foranea/s como
> sigue:
>
> CREATE OR REPLACE FUNCTION "public"."f_TablaClavesPrimyAjenas"
> ("Tabla" varchar) RETURNS SETOF varchar AS
> $body$
> /* Funcion que devuelve Nombre de los campos que son
> clave Primaria y Ajenas */
> begin
> select oid, conname, contype, conrelid, conkey, confkey
> from pg_constraint
> where conrelid = f_Tablaoid(Tabla);
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> Todavia esta incompleta, pero se ve que necesito el oid de la tabla
> para acceder. Ademas no se si mi logica esta bien o falta algo, recien
> comienzo con PL.
>
> La otra funcion seria:
>
> CREATE OR REPLACE FUNCTION "public"."f_Tablaoid" ("Tabla" varchar)
> RETURNS oid AS
> $body$
> /* Funcion que devuelve el oid de una Tabla */
> DECLARE
> Tabla alias for $1;
> begin
> select oid, relname
> from pg_class
> where relname=Tabla;
> return pg_class.oid;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> Pero al ejecutar esta con Postgresql manager lite me devuelve:
>
> ERROR: query has no destination for result data
> HINT: If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT: PL/pgSQL function "f_Tablaoid" line 5 at SQL statement
>
> Algo esta faltando seguramente pero no se que es.
>
>
> El 5/04/07, José Manuel Ruiz <josemanuelruizbaena(at)gmail(dot)com> escribió:
> > Sé que se puede saber esto que pides haciendo select a las que forman el
> > diccionario de datos de tu base de datos. Pero si te conectas con "psql" a
> > tu base de datos (desde la línea de comandos de linux sé que podrás
> > hacerlo
> > "psql <nombreDB>" como usuario postgres). Una vez conectado haces "\d
> > <nombreTabla>" y te devolverá la descripción de la tabla que es lo que
> > creo
> > que buscas (en oracle DESC <nombreTabla>).
> >
> > Un saludo.
> >
> > El día 4/04/07, Blas Lopez <blaslopez(at)gmail(dot)com> escribió:
> > >
> > > Hola, soy un poco nuevo, comence a trabajar con Postgres sobre Windows
> > > en Enero para recibirme de programador.
> > > Estoy con VB6, y ahora necesitaria saber como obtener informacion
> > > sobre una Tabla: campos (columnas), y restricciones (clave principal y
> > > clave foranea).
> > > Es decir ¿que tipo de consulta me devuelve esa información? Aclaro que
> > > todas las tablas se encuentran en el esquema Public y tengo instalado
> > > Postgres 8.2
> > > Desde ya gracias.
> > >
> > > ---------------------------(fin del
> > mensaje)---------------------------
> > > TIP 8: explain analyze es tu amigo
> > >
> >
> >
> >
> > --
> > "Comparte lo que sabes, aprende lo que no sepas"
> > Todo por el conocimiento libre
> >
> > Linux user number 441469
>
> ---------------------------(fin del mensaje)---------------------------
> TIP 8: explain analyze es tu amigo
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 269.0.0/752 - Release Date: 08/04/2007
> 20:34
>
>
>