-- from http://baltaks.com/2015/08/how-to-change-text-fields-to-a-real-uuid-type-for-django-and-postgresql
DO $$
DECLARE
    table_names text[];
    this_table_def text[];
    this_table_name text;
    this_field_name text;
    the_constraint_name text;
    index_names record;

BEGIN

SELECT array[['renkanmanager_workspace', 'workspace_guid'],
             ['renkanmanager_renkan', 'renkan_guid'],
             ['renkanmanager_revision', 'revision_guid']
       ]
    INTO table_names;


FOREACH this_table_def SLICE 1 IN ARRAY table_names
LOOP
    this_table_name := this_table_def[1];
    this_field_name := this_table_def[2];
    RAISE notice 'migrating table %', this_table_name;

    SELECT CONSTRAINT_NAME INTO the_constraint_name
    FROM information_schema.constraint_column_usage
    WHERE CONSTRAINT_SCHEMA = current_schema()
        AND COLUMN_NAME IN (this_field_name)
        AND TABLE_NAME = this_table_name
    GROUP BY CONSTRAINT_NAME
    HAVING count(*) = 1;
    if the_constraint_name is not NULL then
        RAISE notice 'alter table % drop constraint %',
            this_table_name,
            the_constraint_name;
        execute 'alter table ' || this_table_name
            || ' drop constraint ' || the_constraint_name;
    end if;

    FOR index_names IN
    (SELECT i.relname AS index_name
     FROM pg_class t,
          pg_class i,
          pg_index ix,
          pg_attribute a
     WHERE t.oid = ix.indrelid
         AND i.oid = ix.indexrelid
         AND a.attrelid = t.oid
         AND a.attnum = any(ix.indkey)
         AND t.relkind = 'r'
         AND a.attname = this_field_name
         AND t.relname = this_table_name
     ORDER BY t.relname,
              i.relname)
    LOOP
        RAISE notice 'drop index %', quote_ident(index_names.index_name);
        EXECUTE 'drop index ' || quote_ident(index_names.index_name);
    END LOOP; -- index_names

    RAISE notice 'alter table % alter column % type uuid using uuid::uuid;',
        this_table_name, this_field_name;
    execute 'alter table '  || quote_ident(this_table_name)
        || ' alter column ' || quote_ident(this_field_name)
        || ' type uuid using ' || quote_ident(this_field_name) || '::uuid;';
    RAISE notice 'CREATE UNIQUE INDEX %_% ON % (%);',
        this_table_name, this_field_name, this_table_name, this_field_name;
    execute 'create unique index ' || this_table_name || '_' || this_field_name || ' on '
        || this_table_name || '('|| this_field_name ||');';

END LOOP; -- table_names

END;
$$
