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