funcionou?
Em Quinta 19 Outubro 2006 13:59, Thiago Risso escreveu:
> Depois do toque do João.... Fiz alguns ajustezinhos :
>
> CREATE OR REPLACE FUNCTION toLowerTablesAndFields()
> RETURNS text AS
> $BODY$
> DECLARE
> recTables RECORD;
> recFields RECORD;
> lowerTable varchar;
> lowerField varchar;
> table varchar;
> field varchar;
> schema varchar;
> BEGIN
> -- SELECIONA AS TABELAS E ESQUEMAS
> FOR recTables IN SELECT n.nspname,relname FROM pg_catalog.pg_class c
> JOIN
> pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN
> pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r'
> AND n.nspname ='teste' --NOT IN ('pg_catalog', 'pg_toast',
> 'information_schema')
> LOOP
> table := recTables.relname;
> lowerTable := lower(table);
> schema :=recTables.nspname;
> EXECUTE 'ALTER TABLE "'||schema||'"."'||table||'" RENAME TO
> "tmp_'||lowerTable||'"' ;
> EXECUTE 'ALTER TABLE "'||schema||'"."tmp_'||lowerTable||'"
> RENAME TO "'||lowerTable||'"';
>
> -- SELECIONA OS CAMPOS DA TABELAS
> FOR recFields IN SELECT a.attname FROM pg_catalog.pg_attribute
> a JOIN pg_catalog.pg_class
> c on c.oid = a.attrelid WHERE a.attnum > 0 AND NOT
> a.attisdropped AND
> c.relname = table
> LOOP
> field:=recFields.attname;
> lowerField := lower(field);
> EXECUTE 'ALTER TABLE "'||schema||'"."'||lowerTable||'"
> RENAME COLUMN "'||field||'" TO "tmp_'||lowerField||'"';
> EXECUTE 'ALTER TABLE "'||schema||'"."'||lowerTable||'"
> RENAME COLUMN "tmp_'||lowerField||'" TO "'||lowerField||'"';
> END LOOP;
>
>
> END LOOP;
> RETURN 'Acabou!!';
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> Att:
> Thiago Risso
_______________________________________________
Grupo de Usuários do PostgreSQL no Brasil
Antes de perguntar consulte o manual
http://pgdocptbr.sourceforge.net/
Para editar suas opções ou sair da lista acesse a página da lista em:
http://pgfoundry.org/mailman/listinfo/brasil-usuarios
