Re: Please add the ability to add columns into the middle of a table.

On 2016-05-06 12:24 PM, David G. Johnston wrote:
> ​B) For PostgreSQL itself to allow for column re-ordering?
>
> The later has been discussed many times, including relatively recently, but
> hasn't made it into -core (not even sure if patches have been proposed.

I hope that feature makes it into core.  Its long overdue.  Now that UPSERT is 
done with 9.5, this is one of the outstanding features of MySQL that is actually 
useful and Postgres lacks.

One place I found such a feature useful a few years back is when I wanted to 
make a generic auditing trigger reusable for any table where the corresponding 
audit table had a few specified extra columns.

create function save_del_to_history() returns trigger language plpgsql as $$
begin    execute        format( 'insert into %I.%I select $1, $2.*', tg_table_schema, 
concat(tg_table_name,'_history') )        using currval('changeset_id_gen'::regclass), old;    return null;
end
$$;

Using that single procedure I could just do the following for each table "foo":

create trigger save_del_to_history    after update or delete on foo    for each row execute procedure
save_del_to_history();

However this approach is fragile due to the lack of column re-ordering because 
the extra auditing columns have to be the first columns of the audit tables in 
order for this to not break if regular table columns are added to a foo; if the 
audit fields were last they would appear in the middle of the regular field 
list, so the * wouldn't work right; and that in turn constrains being able to 
add or change auditing fields.

The above example was written against Postgres 9.1, I don't know if other 
relevant things changed on newer versions to improve such reusability.

I grant that normally field order shouldn't matter, and in the relational model 
fields aren't ordered, but in SQL they are significant.

-- Darren Duncan