Function unnest :
Thia is used for expanding an array into a set of rows.
I got it from wiki.postgresql.org/wiki/Array_Unnest
CREATE OR REPLACE FUNCTION unnest(anyarray)Function modify_id_type_f:
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE
This is function is used to modify the data type of the primary key columns.
CREATE OR REPLACE FUNCTION modify_id_type_f() RETURNS void AS $$
DECLARE
v_rec RECORD;
BEGIN
FOR v_rec IN
(
select tab_cols.table_name, tab_cols.col_name, tab_cols.col_type ,
'alter table '|| tab_cols.table_name ||' alter '|| tab_cols.col_name ||' type '|| ' bigint ' as str
from
(
select relid as table_id,relname as table_name, a.attname as col_name, t.typname as col_type, a.attnum, a.atttypid
from pg_stat_user_tables as c
inner join pg_attribute as a on c.relid = a.attrelid and a.attnum > 0
inner join pg_type as t on a.atttypid = t.oid
) as tab_cols
inner join
(
select relid as table_id, relname, si.indexrelname, unnest(i.indkey) as ind_cols
from pg_stat_user_indexes as si
inner join pg_index as i on si.indexrelid = i.indexrelid
where i.indisprimary = true
) as ind_cols
on tab_cols.table_id = ind_cols.table_id and tab_cols.attnum = ind_cols.ind_cols
where tab_cols.col_type = 'int4'
)
LOOP
--RAISE NOTICE 'Dname=%',v_rec.str;
EXECUTE v_rec.str;
END LOOP;
END;
$$ LANGUAGE plpgsql;