Re: Obtener campos FK y PK, ahora filrrar por schema

Bueno, luego de investigar un poco encontre como obtener el PK y como
obtener el FX, en el caso de la FK me fue facil pero en el caso de la PK
estoy un poco confundido, traté de seguir el patrón , pero no doy pié con
bola, agradeceré alguna ayuda al respecto.
la idea es filtrar por SCHEMA ademas de TABLA esto se debe a que si existe
más de una tabla con el mismo nombre en diferente schemas me trae solo la
del public.
El código original lo saque de un hilo de Kevin Gordon.

Obtener PK (me falta filtrarlo por schema)
SELECT ic.relname AS index_name, bc.relname AS tab_name,
ta.attname AS column_name, i.indisunique AS unique_key,
i.indisprimary AS primary_key FROM pg_class bc, pg_class ic,
pg_index i, pg_attribute ta, pg_attribute ia
WHERE bc.oid = i.indrelid
AND ic.oid = i.indexrelid
AND ia.attrelid = i.indexrelid
AND ta.attrelid = bc.oid
AND bc.relname = 'ciudad'
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1] ORDER BY index_name, tab_name,
column_name;

Obtener FK (esta funciona ferpecta :) )
GetFK(Schema,Tabla)
SELECT conname,
pg_catalog.pg_get_constraintdef(oid) as condef FROM
pg_catalog.pg_constraint r WHERE r.conrelid = (SELECT c.oid
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname ~ '^ciudad$' AND n.nspname ~ '^public$')
AND r.contype = 'f';

----- Original Message -----
From: Julio Cesar Rodriguez Dominguez
To: Yoel Mc Lennan
Sent: Friday, August 03, 2007 7:17 PM
Subject: Re: [pgsql-es-ayuda] Obtener campos FK y PK

Mira yo hice un PL/PgSql para obtener el PK de una tabla, si te sirve la
puedes modificar para obtener el resto de la información.

-- descripción: regresa el nombre de los campos que forman la llave primaria
de una tabla
-- param: nombre de la tabla
create or replace function getpkey(text) returns integer as '
declare
tabla_oid integer;
real_name text;
tabla alias for $1;
exp_tabla text;
index_def text;
inicio integer;
fin integer;
tmp text;
pos integer;
campo text;
noCampos integer;

begin

noCampos :=0;
exp_tabla:=''^''||tabla||''$'';
pos:=-1;

select array_upper(conkey,1) as no from pg_catalog.pg_constraint where
conname like tabla||''%''
and contype = ''p''into noCampos;

if noCampos is null then
raise notice ''ESTA TABLA NO TIENE LLAVE PRIMARIA'';
return 0;
else
raise notice ''Numero de campos: %'',noCampos;
end if;

select c.oid FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespace n
ON n.oid = c.relnamespace WHERE c.relname ~ exp_tabla AND
pg_catalog.pg_table_is_visible(c.oid)
into tabla_oid;

select c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0,
true)FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid =
tabla_oid AND c.oid =
i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary
DESC,
i.indisunique DESC, c2.relname into real_name,index_def;

raise notice ''real name <--> %'',real_name;
inicio := position(''('' in index_def);
tmp := substring (index_def from inicio+1 for char_length(index_def));
fin := position('')'' in tmp);
tmp := substring (tmp from 0 for fin);

while pos!=0 loop

pos := position('','' in tmp);
if pos = 0 then
campo := substring(tmp from 0 for char_length(tmp)+1);
else
campo := substring(tmp from 0 for pos);
end if;
raise notice ''campo <--> %'',ltrim(campo);
tmp := substring(tmp from pos+1 for char_length(tmp));

end loop;

return tabla_oid;
end;
' language 'plpgsql';

--
Jc3
----- Original Message -----
From: Julio Cesar Rodriguez Dominguez
To: Yoel Mc Lennan
Sent: Friday, August 03, 2007 7:17 PM
Subject: Re: [pgsql-es-ayuda] Obtener campos FK y PK

Mira yo hice un PL/PgSql para obtener el PK de una tabla, si te sirve la
puedes modificar para obtener el resto de la información.

-- descripción: regresa el nombre de los campos que forman la llave primaria
de una tabla
-- param: nombre de la tabla
create or replace function getpkey(text) returns integer as '
declare
tabla_oid integer;
real_name text;
tabla alias for $1;
exp_tabla text;
index_def text;
inicio integer;
fin integer;
tmp text;
pos integer;
campo text;
noCampos integer;

begin

noCampos :=0;
exp_tabla:=''^''||tabla||''$'';
pos:=-1;

select array_upper(conkey,1) as no from pg_catalog.pg_constraint where
conname like tabla||''%''
and contype = ''p''into noCampos;

if noCampos is null then
raise notice ''ESTA TABLA NO TIENE LLAVE PRIMARIA'';
return 0;
else
raise notice ''Numero de campos: %'',noCampos;
end if;

select c.oid FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespace n
ON n.oid = c.relnamespace WHERE c.relname ~ exp_tabla AND
pg_catalog.pg_table_is_visible(c.oid)
into tabla_oid;

select c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0,
true)FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid =
tabla_oid AND c.oid =
i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary
DESC,
i.indisunique DESC, c2.relname into real_name,index_def;

raise notice ''real name <--> %'',real_name;
inicio := position(''('' in index_def);
tmp := substring (index_def from inicio+1 for char_length(index_def));
fin := position('')'' in tmp);
tmp := substring (tmp from 0 for fin);

while pos!=0 loop

pos := position('','' in tmp);
if pos = 0 then
campo := substring(tmp from 0 for char_length(tmp)+1);
else
campo := substring(tmp from 0 for pos);
end if;
raise notice ''campo <--> %'',ltrim(campo);
tmp := substring(tmp from pos+1 for char_length(tmp));

end loop;

return tabla_oid;
end;
' language 'plpgsql';

--
Jc3
----- Original Message -----
From: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
To: "Yoel Mc Lennan" <webmaster(at)yoel(dot)com(dot)ar>
Cc: <pgsql-es-ayuda(at)postgresql(dot)org>
Sent: Friday, August 03, 2007 6:58 PM
Subject: Re: [pgsql-es-ayuda] Obtener campos FK y PK

Yoel Mc Lennan escribió:
> Hola grupo, se que había un thread al respecto pero no lo encuentro.
>
> Necesito obtener los campos de una tabla dada y el tipo de campo (Int32,
> varchar, etc) además de saber que capos son Pk y que campos son FK con sus
> respectivas relaciones, osea que tabla es la relacionada a ese FK
>
> tengo algo como esto que me devuelve el tipo de campo, pero no si es pk o
> fk y su relacion en caso de ser fk
>
> SELECT attname, typname From pg_namespace, pg_attribute, pg_type,
> pg_class
> Where pg_type.oid = atttypid AND pg_class.oid = attrelid AND
> pg_namespace.nspname ='public'
> AND relnamespace = pg_namespace.oid AND relname ='ciudad' AND attnum >=
> 1;

Prueba incluyendo pg_constraint y/o pg_index.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13'
56.4"
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad;
jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)