1
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
1
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
0
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;
but I bumped into the dependencies in the views... :( ERROR: can’t change the type of a column used by a view or rule
Browser other questions tagged postgresql
You are not signed in. Login or sign up in order to post.
which database? mysql, sql, oracle?
– RBoschini
postgres..
– Felipe Sachetti