change field type of all tables

Question

there is some way to go through the tables of a database, changing all columns of a certain type?

type used now DOUBLE PRECISION desired type NUMERIC

Answer 1

CREATE OR REPLACE FUNCTION AlteraTipoDoubleNumeric()
RETURNS text AS
$BODY$
DECLARE 
    recTables RECORD;
    recFields RECORD;
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 ='public' --NOT IN ('pg_catalog', 'pg_toast',     'information_schema') 
    LOOP
    FOR recFields IN SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as tipodedado 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 = recTables.relname and pg_catalog.format_type(a.atttypid, a.atttypmod) = 'double precision'
    LOOP
        raise notice '%, %, %', recTables.relname, recFields.attname, recFields.tipodedado;
        execute 'alter table ' || recTables.relname || ' alter column ' || recFields.attname || ' type numeric (10, 6) using ' || recFields.attname || '::numeric';
    END LOOP;
    END LOOP; 
    RETURN 'Acabou!!';
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;