/******************************************************************************** CATALOG_TABLE_COLUMN_MOVE Function
********************************************************************************create or replace function _utility.catalog_table_column_move
(
    strSchemaName text, 
    strTableName text, 
    strColumnName text, 
    strColumnNameBefore text
)
    returns void as $$
declare
    rIndex record;
    rConstraint record;
    rColumn record;
    strSchemaTable text = strSchemaName || '.' || strTableName;
    strDdl text;
    strClusterIndex text;
begin
    -- Raise notice that a reorder is in progress
    raise notice 'Reorder columns in table %.% (% before %)', strSchemaName, strTableName, strColumnName, strColumnNameBefore;

    -- Get the cluster index
    select pg_index.relname
      into strClusterIndex
      from pg_namespace
           inner join pg_class
                on pg_class.relnamespace = pg_namespace.oid
               and pg_class.relname = strTableName
           inner join pg_index pg_index_map
                on pg_index_map.indrelid = pg_class.oid
               and pg_index_map.indisclustered = true
           inner join pg_class pg_index
                on pg_index.oid = pg_index_map.indexrelid
     where pg_namespace.nspname = strSchemaName;

    if strClusterIndex is null then
        raise exception 'Table %.% must have a cluster index before reordering', strSchemaName, strTableName;
    end if;

    -- Disable all user triggers
    strDdl = 'alter table ' || strSchemaTable || ' disable trigger user';
    raise notice '        Disable triggers [%]', strDdl;
    execute strDdl;

    -- Create temp table to hold ddl
    create temp table temp_catalogtablecolumnreorder
    (
        type text not null,
        name text not null,
        ddl text not null
    );

    -- Save index ddl in a temp table
    raise notice '    Save indexes';

    for rIndex in
        with index as
        (
            select _utility.catalog_index_list_get(strSchemaName, strTableName) as name
        ),
        index_ddl as
        (
            select index.name,
                   _utility.catalog_index_create_get(_utility.catalog_index_get(strSchemaName, index.name)) as ddl
              from index
        )
        select index.name,
               index_ddl.ddl
          from index
               left outer join index_ddl
                    on index_ddl.name = index.name
                   and index_ddl.ddl not like '%[function]%'
    loop
        raise notice '        Save %', rIndex.name;
        insert into temp_catalogtablecolumnreorder values ('index', rIndex.name, rIndex.ddl);
    end loop;

    -- Save constraint ddl in a temp table
    raise notice '    Save constraints';

    for rConstraint in
        with constraint_list as
        (
            select _utility.catalog_constraint_list_get(strSchemaName, strTableName, '{p,u,f,c}') as name
        ),
        constraint_ddl as
        (
            select constraint_list.name,
                   _utility.catalog_constraint_create_get(_utility.catalog_constraint_get(strSchemaName, strTableName, 
                                                                                          constraint_list.name)) as ddl
              from constraint_list
        )
        select constraint_list.name,
               constraint_ddl.ddl
          from constraint_list
               left outer join constraint_ddl
                    on constraint_ddl.name = constraint_list.name
    loop
        raise notice '        Save %', rConstraint.name;
        insert into temp_catalogtablecolumnreorder values ('constraint', rConstraint.name, rConstraint.ddl);
    end loop;

    -- Move column
    for rColumn in
        with table_column as
        (
            select pg_attribute.attname as name,
                   rank() over (order by pg_attribute.attnum) as rank,
                   pg_type.typname as type,
                   case when pg_attribute.atttypmod = -1 then null else ((atttypmod - 4) >> 16) & 65535 end as precision,
                   case when pg_attribute.atttypmod = -1 then null else (atttypmod - 4) & 65535 end as scale,
                   not pg_attribute.attnotnull as nullable,
                   pg_attrdef.adsrc as default,
                   pg_attribute.*
              from pg_namespace
                   inner join pg_class
                        on pg_class.relnamespace = pg_namespace.oid
                       and pg_class.relname = strTableName
                   inner join pg_attribute
                        on pg_attribute.attrelid = pg_class.oid
                       and pg_attribute.attnum >= 1
                       and pg_attribute.attisdropped = false
                   inner join pg_type
                        on pg_type.oid = pg_attribute.atttypid
                   left outer join pg_attrdef
                        on pg_attrdef.adrelid = pg_class.oid
                       and pg_attrdef.adnum = pg_attribute.attnum
             where pg_namespace.nspname = strSchemaName
             order by pg_attribute.attnum
        )
        select table_column.*
          from table_column table_column_before
               inner join table_column
                    on table_column.rank >= table_column_before.rank
                   and table_column.name <> strColumnName
         where table_column_before.name = strColumnNameBefore
    loop
        raise notice '    Move column %', rColumn.name;

        strDdl = 'alter table ' || strSchemaTable || ' rename column "' || rColumn.name || '" to "@' || rColumn.name || '@"';
        raise notice '        Rename [%]', strDdl;
        execute strDdl;
        
        strDdl = 'alter table ' || strSchemaTable || ' add "' || rColumn.name || '" ' || rColumn.type ||
                 case when rColumn.precision is not null then '(' || rColumn.precision || ', ' || rColumn.scale || ')' else '' end;
        raise notice '        Create [%]', strDdl;
        execute strDdl;
        
        strDdl = 'update ' || strSchemaTable || ' set "' || rColumn.name || '" = "@' || rColumn.name || '@"';
        raise notice '        Copy [%]', strDdl;
        execute strDdl;

        strDdl = 'alter table ' || strSchemaTable || ' drop column "@' || rColumn.name || '@"';
        raise notice '        Drop [%]', strDdl;
        execute strDdl;

        if rColumn."default" is not null then
            strDdl = 'alter table ' || strSchemaTable || ' alter column "' || rColumn.name || '" set default ' || rColumn.default;
            raise notice '        Default [%]', strDdl;
            execute strDdl;
        end if;

        if rColumn.nullable = false then
            strDdl = 'alter table ' || strSchemaTable || ' alter column "' || rColumn.name || '" set not null';
            raise notice '        Not Null [%]', strDdl;
            execute strDdl;
        end if;
    end loop;

    -- Rebuild indexes
    raise notice '    Rebuild indexes';

    for rIndex in
        select name,
               ddl
          from temp_catalogtablecolumnreorder
         where type = 'index'
    loop
        begin
            execute rIndex.ddl;
            raise notice '        Rebuild % [%]', rIndex.name, rIndex.ddl;
        exception
            when duplicate_table then
                raise notice '        Skip % [%]', rIndex.name, rIndex.ddl;
        end;
    end loop;

    -- Rebuild constraints
    raise notice '    Rebuild constraints';
    
    for rConstraint in
        select name,
               ddl
          from temp_catalogtablecolumnreorder
         where type = 'constraint'
    loop
        begin
            execute rConstraint.ddl;
            raise notice '        Rebuild % [%]', rConstraint.name, rConstraint.ddl;
        exception
            when duplicate_object or duplicate_table or invalid_table_definition then
                raise notice '        Skip % [%]', rConstraint.name, rConstraint.ddl;
        end;
    end loop;

    -- Recluster table
    strDdl = 'cluster ' || strSchemaTable || ' using ' || strClusterIndex;
    raise notice '    Recluster [%]', strDdl;
    execute strDdl;

    -- Enable all user triggers
    strDdl = 'alter table ' || strSchemaTable || ' enable trigger user';
    raise notice '    Enable triggers [%]', strDdl;
    execute strDdl;

    -- Drop temp tables
    drop table temp_catalogtablecolumnreorder;
end
$$ language plpgsql security invoker;

comment on function _utility.catalog_table_column_move(text, text, text, text) is
'Moves a column before another column in a table.  For example:

{{perform _utility.catalog_table_column_move(''attribute'', ''attribute'', ''target'', ''active'');}}

will position the "target" column right before the "active" column.  It''s not currently possible to directly move a column to the
right but this can be achieved by multiple moves of columns to the left.

There are a few caveats:
* The table must have a cluster index.  Moving columns is messy on the storage and the table needs to be re-clustered afterwards.
* Column referencing triggers will not automatically be dropped or rebuilt.
* Column specific permissions are not restored after the move.
* A column cannot be moved before the primary key if there are foreign key references from other tables.';