Re: change natural column order - Mailing list pgsql-general

Daniel Martini zei:
> Hi,
>
> Joolz, you already got quite a few answers, that the frontend is
> probably
> not properly designed, if it relies on a certain column ordering. I
> agree

Hi Daniel,

Well, I made the frontend myself, so... :)

There is a reason that I made it this way, I have a database with a
lot of different tables and I wanted the frontend to be as versatile
as possible, so I wouldn't have to write PHP frontend functions for
each table or change the SELECT statements that generate the data
for the frontend each time a column is added (and this will happen).
So my application does things like this (semi-code):

$exclude_columns = {"oid, ""audit_column_one", "audit_column_two"};

function one() {
  $sql = "select * from fubar";
  two($sql);
}

function two() {
  // make array from $sql
  // remove elements that exist in $exclude_columns
  // show array
}

> completely with that. However your question got me curious, and I've
> digged
> around a bit in the system tables. You might be interested in my
> findings.
> See below.

BTW I found out that my questions is not as weird as I expected it
to be. MySQL can do it (AFTER clause), Firebird too, and without a
doubt others like Oracle and DB2 too.

> Citing Joolz <joolz@arbodienst-limburg.nl>:
>> I agree. Only I think this wouldn't require new functionality, I
>> have a gut feeling that this is possible as it is. Now only find
>> out
>> how :)
>>
>> I'll have a look at the system tables (that's where the answer
>> must
>> be) but maybe someone who has done this can save me the time...
>
> If you do:
> set search_path=information_schema;
> \d columns
> and look at the Columns defined, you'll find a column called
> ordinal_position,
> which incidentally corresponds to the position of the columns on
> output. If
> you dig a bit further and look at the definition of the columns
> view, you'll
> find, that this column comes from a column attnum in pg_attribute.
> As
> database superuser, you can actually change the values of attnum,
> however
> doing so results in:
> ERROR:  invalid memory alloc request size 4294967295
> on queries on the tables for which you changed attnum. So:
> 1.) obviously PostgreSQL does not like it at all (at least on my
> platform,
>     which is OpenBSD 3.6)
> 2.) I wouldn't risk messing with a system table, which I can only
> write to
>     if I'm superuser if I don't completely understand what's
> happening
>     behind the scenes (at least not for production use).
> 3.) changing that behaviour is probably a lot more work than
> changing the
>     frontend.

Yes, if I understand all the threads correctly, attnum is somehow
bound to the physical location of the data it represents. That makes
it almost impossible to fiddle around with it. Someone proposed to
make an extra field attpos, but it doesn't look like this will be
happening soon.