-- Autor: Julio Cesar Rodriguez Dominguez -- 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';