Is there any way to go through the tables in a database by changing all the columns of a given type?
type used now DOUBLE PRECISION desired type NUMERIC
Is there any way to go through the tables in a database by changing all the columns of a given type?
type used now DOUBLE PRECISION desired type NUMERIC
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;