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
