Saturday, October 16, 2010

Modify all primary key ID columns' data type in Postgresql 8.1

0 comments
I had to modify all primary key ID columns' data type from integer to biginteger in Postgresql 8.1. I created two functions for this purpose.

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)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$BODY$
  LANGUAGE 'sql' IMMUTABLE
Function modify_id_type_f:
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;

Leave a Reply

Labels