> 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