Re: Column reordering in pg_dump

> The ultimate conclusion was that a three-way split (identity, logical
> position, physical position) could work because most of the code only
> cares about column identity; the places where logical or physical
> positions are important are pretty narrowly circumscribed, or could
> be made so.

I started to take a look at this at one point and quickly got
intimidated. Do you have any sense of what sort of refactoring would
be required to make this viable?

I believe that the original discussion[1] may have somewhat
underestimated the number of places where logical position is
relevant. The list includes at least:

SELECT * FROM foo;
TABLE foo;
INSERT INTO foo VALUES (...) (or SELECT, but without column list
COPY foo FROM 'foo';
COPY foo TO 'foo';

There are also some problems with this syntax:

alias (column_alias, column_alias, column_alias)

Imagine for example:

CREATE TABLE foo (c1 integer, c2 text, c3 boolean, c4 date, c5
timestamp, c6 numeric, c7 varchar);
CREATE OR REPLACE VIEW tricky AS SELECT * FROM foo AS bar (a, b, c);
ALTER TABLE foo ALTER COLUMN c2 POSITION LAST;

After some thought, it seems pretty clear, at least to me, that the
third (hypothetical) command should not change the result of "SELECT *
FROM tricky" (the contrary conclusion gives rise to a lot of problems,
especially if there are other views depending on it). But what will
"pg_dump -t tricky" output at this point? I suspect it will be
necessary to introduce some new syntax here.

...Robert

[1] http://archives.postgresql.org/pgsql-hackers/2006-12/msg00977.php