Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
trigger.

Which would that be? The statement that directly invoked the trigger,
or the one the user typed, or would you want a list of all of them?

This should be able to be used in row- or statement-level
triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to
return the valid statement that operates on that row only. For example
the actual statement:
UPDATE inventory SET status = 0 WHERE status = 1;
would be rewritten as:
UPDATE inventory SET status = 0 WHERE id = 2335;
when accessed from within a row-level trigger for the row who's
primary key (id) equals 2335.

Why, when NEW has all the info you need in a much easier to use format?
Seems pretty pointless to me

2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
would return varchar 'false' while:
false::integer
would return integer 0. Currently there seems to be no way to
typecast from boolean (please correct me if I'm wrong). This is quite
disappointing since you can typecast into boolean.

So make them? It's not like it's hard:

CREATE CAST (boolean AS varchar) USING FUNCTIN bool_to_varchar(bool);

4.
The ability to view the DDL for objects. Logically I know that this HAS
to be possible already but I can't figure it out and a search of the
documentation doesn't mention it. You can do this in EMS PostgreSQL
Manager but I can't figure out how to query it on my own.

psql gives you that. If you give -E it'll even show you the queries it
uses to make the info. Also, the information_schema should have most
stuff you want.

5.
The SET and ENUM data types. I know MySQL is cheap and evil but even it
has them. Both are really just Integers attached to some Metadata. You
have no idea how many descriptor tables I have for simple enumerations.
Some have less than 10 items in them!

Someone actually mosted a patch that did this. Funnily enough, it'd
probably be implemented by creating seperate tables for each ENUM to do
the lookup. It's just suger-coating really

6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. at least other databases on the same
server. Granted it might not be possible to JIN, UNIN or Subquery
against them but I'd at least like to be able to perform a query and
work with the results. We currently have to feed a postgresql database
daily snapshots the live Microsoft SMS network data using a DTS
package. Being able to access the Live data (especially if we could
join against it) would be awesome.

dblink does it for postgres DBs, there are similar modules for
connections to other databases.

8.
The ability to use procedural-language extensions everywhere, not just
in functions.

Like where? Give an example.

9.
The ability to nest fields within fields. For example:
PERSN
NAME
LAST
FIRST
PHNE

You can sort of do this, using rowtypes. Havn't nested more than one
level though. Not sure why you'd want this though. A database stores
data, presentation is the application's job.

10.
an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
SELECT balance, name FRM customers WHERE balance < 0;
where balance actually performs a behind the scenes JIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view.

You are wrong, you can make updatable views.

example. For example:
In: 123 456-7890
: (123) 456-7890
Stored As:
PHNE = (Virtual Function, with Regexp input parser)
AREA_CDE = 123
PREFIX = 456
SUFFIX = 7890
It would be interesting. Combine with item 9 above and you can make
"name" output in a structured format like "Last, First". Vb.Net's IDE
does this in the properties list for nested properties.

So, create a type that does that. PostgreSQL is extensible. It's got
data types for ISBNs, Internet addresses and even an XML document type.
Compared to that a simple phone number field would be trivial.

Have a nice day,

>10.
>an alternative to views where tables can be defined with virtual
>fields which point to functions. So for example I can say:
>SELECT balance, name FRM customers WHERE balance < 0;
>where balance actually performs a behind the scenes JIN against a
>transactions table and totals the customers credits and debits. I
>realize views can do this but for adding a single dynamic field they
>are cumbersome and correct me if I'm wrong but I don't think you can
>UPDATE against a view.

You are wrong, you can make updatable views.

Yes you can but not out of the box. You have to write some rules to make this
go, isn't it ?

Aly.

>You are wrong, you can make updatable views.

Yes you can but not out of the box. You have to write some rules to
make this go, isn't it ?

I think the point is that out of the box, yes you can create an
updateable view using rules.

You can not just say: create view and have it updateable but the rules
are rather trivial.

Joshua D. Drake

Aly.

1/13/06, Aly Dharshi <aly.dharshi (AT) telus (DOT) netwrote:
>
>
>10.
>an alternative to views where tables can be defined with virtual
>fields which point to functions. So for example I can say:
>SELECT balance, name FRM customers WHERE balance < 0;
>where balance actually performs a behind the scenes JIN against a
>transactions table and totals the customers credits and debits. I
>realize views can do this but for adding a single dynamic field they
>are cumbersome and correct me if I'm wrong but I don't think you can
>UPDATE against a view.
>
You are wrong, you can make updatable views.

Yes you can but not out of the box. You have to write some rules to make this
go, isn't it ?

Aly.

Fri, Jan 13, 2006 at 02:23:29PM +0100, Martijn van wrote:
Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered
the trigger.

Which would that be? The statement that directly invoked the
trigger, or the one the user typed, or would you want a list of all
of them?

This would be pretty cool either way.

4.
The ability to view the DDL for objects. Logically I know that
this HAS to be possible already but I can't figure it out and a
search of the documentation doesn't mention it. You can do this in
EMS PostgreSQL Manager but I can't figure out how to query it on
my own.

psql gives you that. If you give -E it'll even show you the queries
it uses to make the info. Also, the information_schema should have
most stuff you want.

Actually, this is a TD :)

5.
The SET and ENUM data types. I know MySQL is cheap and evil but
even it has them. Both are really just Integers attached to some
Metadata. You have no idea how many descriptor tables I have for
simple enumerations. Some have less than 10 items in them!

Someone actually mosted a patch that did this. Funnily enough, it'd
probably be implemented by creating seperate tables for each ENUM to
do the lookup. It's just suger-coating really

But it's *tasty* sugar coating, and you don't have to mess around with
extra messing to get the ordering you've set.

6.
Cross database queries. I'd like to be able to query a MS SQL
Server database from within PL/PGSQL. at least other databases
on the same server. Granted it might not be possible to JIN,
UNIN or Subquery against them but I'd at least like to be able to
perform a query and work with the results. We currently have to
feed a postgresql database daily snapshots the live Microsoft SMS
network data using a DTS package. Being able to access the Live
data (especially if we could join against it) would be awesome.

dblink does it for postgres DBs, there are similar modules for
connections to other databases.

DBI-Link for other data sources.

8.
The ability to use procedural-language extensions everywhere, not just
in functions.

Like where? Give an example.

I'm thinking in-line anonymous blocks of your favorite PL.

9.
The ability to nest fields within fields. For example:
PERSN
NAME
LAST
FIRST
PHNE

You can sort of do this, using rowtypes. Havn't nested more than one
level though. Not sure why you'd want this though. A database stores
data, presentation is the application's job.

WITH RECURSIVE is on the TD list. Maybe he's referring to that.

So, create a type that does that. PostgreSQL is extensible. It's got
data types for ISBNs, Internet addresses and even an XML document
type. Compared to that a simple phone number field would be
trivial.

I'd say a phone number is *much* harder to do right if you're storing
phone numbers from more than one country.

Cheers,
D

Martijn van wrote:
Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
trigger.

Which would that be? The statement that directly invoked the trigger,
or the one the user typed, or would you want a list of all of them?

TG_STATEMENT would be the original statement entered by the user which
caused the trigger to execute exactly as it was entered.
TG_EFFECTIVE_STATEMENT would be the statement re-written for that row
only (the row's primary key would be identified in the WHERE clause).

This should be able to be used in row- or statement-level
triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to
return the valid statement that operates on that row only. For example
the actual statement:
UPDATE inventory SET status = 0 WHERE status = 1;
would be rewritten as:
UPDATE inventory SET status = 0 WHERE id = 2335;
when accessed from within a row-level trigger for the row who's
primary key (id) equals 2335.

Why, when NEW has all the info you need in a much easier to use format?
Seems pretty pointless to me

Auditing with row granularity. So that managers can see what queries
are being run against the database and impliment per-row rollback
capabilities to the system.

2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
would return varchar 'false' while:
false::integer
would return integer 0. Currently there seems to be no way to
typecast from boolean (please correct me if I'm wrong). This is quite
disappointing since you can typecast into boolean.

So make them? It's not like it's hard:

CREATE CAST (boolean AS varchar) USING FUNCTIN bool_to_varchar(bool);

This is a privilaged operation. I don't own the type pg_catalog.boolean
on our production server and therefore cannot create a cast for it.

4.
The ability to view the DDL for objects. Logically I know that this HAS
to be possible already but I can't figure it out and a search of the
documentation doesn't mention it. You can do this in EMS PostgreSQL
Manager but I can't figure out how to query it on my own.

psql gives you that. If you give -E it'll even show you the queries it
uses to make the info. Also, the information_schema should have most
stuff you want.

Thats basically what I've been doing but using the EMS PostgreSQL front
end.

5.
The SET and ENUM data types. I know MySQL is cheap and evil but even it
has them. Both are really just Integers attached to some Metadata. You
have no idea how many descriptor tables I have for simple enumerations.
Some have less than 10 items in them!

Someone actually mosted a patch that did this. Funnily enough, it'd
probably be implemented by creating seperate tables for each ENUM to do
the lookup. It's just suger-coating really

Thats true enough.

6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. at least other databases on the same
server. Granted it might not be possible to JIN, UNIN or Subquery
against them but I'd at least like to be able to perform a query and
work with the results. We currently have to feed a postgresql database
daily snapshots the live Microsoft SMS network data using a DTS
package. Being able to access the Live data (especially if we could
join against it) would be awesome.

dblink does it for postgres DBs, there are similar modules for
connections to other databases.

I'll look into it. This is a hot topic here among even the managers so
there might be a chance they'd impliment it. Thanks!

8.
The ability to use procedural-language extensions everywhere, not just
in functions.

Like where? Give an example.

// PHP
rows = pg_query('IF THEN ENDIF;');

9.
The ability to nest fields within fields. For example:
PERSN
NAME
LAST
FIRST
PHNE

You can sort of do this, using rowtypes. Havn't nested more than one
level though. Not sure why you'd want this though. A database stores
data, presentation is the application's job.

Really I was dreaming up some way to organize the data in large tables.
The only reason I mention it is that we have a table with 90 fields and
looking at it is an eye-soar. :o)

10.
an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
SELECT balance, name FRM customers WHERE balance < 0;
where balance actually performs a behind the scenes JIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view.

You are wrong, you can make updatable views.

example. For example:
In: 123 456-7890
: (123) 456-7890
Stored As:
PHNE = (Virtual Function, with Regexp input parser)
AREA_CDE = 123
PREFIX = 456
SUFFIX = 7890
It would be interesting. Combine with item 9 above and you can make
"name" output in a structured format like "Last, First". Vb.Net's IDE
does this in the properties list for nested properties.

So, create a type that does that. PostgreSQL is extensible. It's got
data types for ISBNs, Internet addresses and even an XML document type.
Compared to that a simple phone number field would be trivial.

Actually I might try to have a go at it just for fun at home. Here at
work I just don't have the ability to create types (AFAIK).

Have a nice day,

"rlee0001" <robeddielee (AT) hotmail (DOT) comwrites:
>example. For example:
>In: 123 456-7890
>: (123) 456-7890
>Stored As:
>PHNE = (Virtual Function, with Regexp input parser)
>AREA_CDE = 123
>PREFIX = 456
>SUFFIX = 7890
>It would be interesting. Combine with item 9 above and you can make
>"name" output in a structured format like "Last, First". Vb.Net's IDE
>does this in the properties list for nested properties.
>>
>So, create a type that does that. PostgreSQL is extensible. It's got
>data types for ISBNs, Internet addresses and even an XML document type.
>Compared to that a simple phone number field would be trivial.
>
Actually I might try to have a go at it just for fun at home. Here at
work I just don't have the ability to create types (AFAIK).

The trouble with the phone number idea is that the above doesn't match
with any relevant standards.

The one thing that *would* match a standard would be ITU-T
Recommendation E.164: "The International Public Telecommunication
Numbering Plan", May 1997.

2.5. Telephone Numbers

Contact telephone number structure is derived from structures defined
in [E164a]. Telephone numbers described in this mapping are
character strings that MUST begin with a plus sign ("+", ASCII value
0x002B), followed by a country code defined in [E164b], followed by a
dot (".", ASCII value 0x002E), followed by a sequence of digits
representing the telephone number. An optional "x" attribute is
provided to note telephone extension information.

Thus, the structure would break the phone number into three pieces:

1. Country code
2. Telephone number
3. Extension information (optional)

My phone number, in EB164 form, looks like:
+01.4166734124

What you seem to be after, here, would confine your telno formatting
to telephone numbers for Canada and the United States, and would break
any time people have a need to express telephone numbers outside those
two countries.

It would be quite interesting to add an EB164 type, as it could
represent phone numbers considerably more compactly than is the case
for plain strings. The 20 digits permissible across 1. and 2. could
be encoded in 68 bits.

Mon, Jan 16, 2006 at 12:13:15PM -0500, Chris Browne wrote:
What you seem to be after, here, would confine your telno formatting
to telephone numbers for Canada and the United States, and would break
any time people have a need to express telephone numbers outside those
two countries.

It would be quite interesting to add an EB164 type, as it could
represent phone numbers considerably more compactly than is the case
for plain strings. The 20 digits permissible across 1. and 2. could
be encoded in 68 bits.

And it would be trivial to provide functions to map that into the
customary format for various countries. In fact, since there's 4 bits
left over, it might be possible to encode the formatting used for the
number in the storage itself.

BTW, you sure about 68 bits? That doesn't seem to allow for a full 20
digit number; or are there restrictions on the max value for one of the
fields?

jnasby (AT) pervasive (DOT) com ("Jim C. Nasby") writes:
Mon, Jan 16, 2006 at 12:13:15PM -0500, Chris Browne wrote:
>What you seem to be after, here, would confine your telno formatting
>to telephone numbers for Canada and the United States, and would break
>any time people have a need to express telephone numbers outside those
>two countries.
>
>It would be quite interesting to add an EB164 type, as it could
>represent phone numbers considerably more compactly than is the case
>for plain strings. The 20 digits permissible across 1. and 2. could
>be encoded in 68 bits.
>
And it would be trivial to provide functions to map that into the
customary format for various countries. In fact, since there's 4 bits
left over, it might be possible to encode the formatting used for the
number in the storage itself.

BTW, you sure about 68 bits? That doesn't seem to allow for a full 20
digit number; or are there restrictions on the max value for one of the
fields?

Hmm.

[3](let ((ttl 1)) (loop
for i from 1 to 68
do (setf ttl (* ttl 2))
do (format t "n=~D 2^n=~D~%" i ttl)))
n=1 2^n=2
n=2 2^n=4
n=3 2^n=8
n=4 2^n=16
n=5 2^n=32
n=6 2^n=64
n=7 2^n=128
n=8 2^n=256
n=9 2^n=512
boring bits elided :-)
n=60 2^n=1152921504606846976
n=61 2^n=2305843009213693952
n=62 2^n=4611686018427387904
n=63 2^n=9223372036854775808
n=64 2^n=18446744073709551616
n=65 2^n=36893488147419103232
n=66 2^n=73786976294838206464
n=67 2^n=147573952589676412928
n=68 2^n=295147905179352825856
NIL

Actually, we pass 10^20 at 2^67, so I went 1 bit too far. We could,
in principle, get 20 digits in 67 bits. Not that this is necessarily
the wisest move.

That's a bit over 8 bytes, which is certainly more space-efficient
than 20 bytes. It's certainly an open question whether that
efficiency is actually worth anything.

My sense is that it would be better to use something equivalent to BCD
(which is what we do with NUMERIC), packing two digits per byte, and
have two "segments" to the telno, a country code segment, and a "local
number" segment. Using BCD, this would occupy 10 bytes, which, by
magical happenstance, is the same size as a US/Canada phone number.

The real questions are how to deal with:

a) Parsing incoming data, since formats people use vary so inanely

Note: I spent most of yesterday dealing with this very issue,
writing up a total of 31 eye-destroying regular expressions to
generate a pl/tcl function to parse cases that I had handy I
daresay that even with so many regexps, I was _still_ left with a
reasonably material number that would not be parsed

b) What sorts of extra operators would be interesting to add in.

Extracting country code is an obvious thing. Applying formatting
rules based on country code is another.

Tue, Jan 17, 2006 at 10:28:03AM -0600, Tony Caduto wrote:
As long as we are talking wish lists

What I would like to see is some way to change the ordering of the
fields without having to drop and recreate the table.

Why are you asking us to optimize the 'SELECT *' case which almost
never belongs in production code in the 1st place?

unlessis it possible to safely modify the |attnum field of
||pg_attribute? I know we should never directly modify system
tables but|

You're right. No 'but' involved :)

Cheers,
D

8.
The ability to use procedural-language extensions everywhere, not just
in functions.
Like where? Give an example.
// PHP
rows = pg_query('IF THEN ENDIF;');
// PHP
rows = pg_query('SELECT CASE WHEN ELSE END;');

Tue, Jan 17, 2006 at 11:35:05AM -0500, Chris Browne wrote:
Note: I spent most of yesterday dealing with this very issue,
writing up a total of 31 eye-destroying regular expressions to
generate a pl/tcl function to parse cases that I had handy I
daresay that even with so many regexps, I was _still_ left with a
reasonably material number that would not be parsed

I hope you can post that somewhere for others to use surely it could
save a lot of people some time

maybe not; I suspect most people just punt on phone numbers, or force
them to a very strict format.

jnasby (AT) pervasive (DOT) com ("Jim C. Nasby") writes:
Tue, Jan 17, 2006 at 11:35:05AM -0500, Chris Browne wrote:
>Note: I spent most of yesterday dealing with this very issue,
>writing up a total of 31 eye-destroying regular expressions to
>generate a pl/tcl function to parse cases that I had handy I
>daresay that even with so many regexps, I was _still_ left with a
>reasonably material number that would not be parsed
>
I hope you can post that somewhere for others to use surely it could
save a lot of people some time

maybe not; I suspect most people just punt on phone numbers, or force
them to a very strict format.

The trouble is, the rules wind up being all too context-sensitive.

The problems I run into with telnos coming from one source differ from
the problems with telnos coming from another.

I suppose perhaps I should "collect the whole set" and see if I can
have some common heuristics that will cope well with all of them

Alas, it's pretty gory, and there's never time :-(.

Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:
Tue, Jan 17, 2006 at 10:28:03AM -0600, Tony Caduto wrote:
As long as we are talking wish lists

What I would like to see is some way to change the ordering of the
fields without having to drop and recreate the table.

Why are you asking us to optimize the 'SELECT *' case which almost
never belongs in production code in the 1st place?
Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

woensdag 18 januari 2006 08:10, schreef Joost Kraaijeveld:
Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

Ter info, ik snap je vraag om ondersteuning van dit 'geintje' middels een
regulier system command, maar als het erg belangrijk is voor je of
incidenteel valt het nu ook te editten via de system tables. Iets van
'ordinal position' in de table/field definitions, maar het is al erg lang
geleden dat ik daar mee gespeeld heb ;) Je kunt dit terugvinden in 'select *
from information_schema.columns', maar je zult zelf even terug moeten zoeken
welke systeemtabellen daar bij zijn betrokken, ik denk overigens dat dit
pg_attribute.attnum is. Denk eraan dat dit soort wijzigingen in de
systeemtabellen zonder garantie komen ;)

Gr.

Frank.

Wed, Jan 18, 2006 at 09:10:23AM +0100, ir. F.T.M. van Vugt bc. wrote:
woensdag 18 januari 2006 08:10, schreef Joost Kraaijeveld:
Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

<snip>

geleden dat ik daar mee gespeeld heb ;) Je kunt dit terugvinden in 'select *
from information_schema.columns', maar je zult zelf even terug moeten zoeken
welke systeemtabellen daar bij zijn betrokken, ik denk overigens dat dit
pg_attribute.attnum is. Denk eraan dat dit soort wijzigingen in de
systeemtabellen zonder garantie komen ;)

Just to avoid anyone getting strange ideas: editting the catalog
manually has a 100% certainty of trashing any data in the table. Also,
various things like indexes and foreign keys may use the attnum also
and become non-functional. It's entirely possible that on an empty
table with nothing attached that changing attnum manually might work,
but I wouldn't bet on it.

Have a nice day.

As a service for the non-dutch speaking people, the abstract of Frank's
comment (hi Frank ;-)):

Wed, 2006-01-18 at 09:10 +0100, ir. F.T.M. van Vugt bc. wrote:
woensdag 18 januari 2006 08:10, schreef Joost Kraaijeveld:
Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

Ter info, ik snap je vraag om ondersteuning van dit 'geintje' middels een
regulier system command, maar als het erg belangrijk is voor je of
incidenteel valt het nu ook te editten via de system tables. Iets van
'ordinal position' in de table/field definitions, maar het is al erg lang
geleden dat ik daar mee gespeeld heb ;) Je kunt dit terugvinden in 'select *
from information_schema.columns', maar je zult zelf even terug moeten zoeken
welke systeemtabellen daar bij zijn betrokken, ik denk overigens dat dit
pg_attribute.attnum is. Denk eraan dat dit soort wijzigingen in de
systeemtabellen zonder garantie komen ;)

If the order of the columns is *really* important, than one could try to
change the system tables.

My point is not that I really want it, but that i can see reasons why
one could want it. Especially if one must learn how an application
interacts with a database, it is nice te be able to see the relation
between a class, it's attributes and the tables with the columns.

L.S.

I was afraid something like this would happen ;)

Just to be clear on the matter, the wrong post was just part of a conversation
between the P and I. We are not exactly strangers and there was no
intentional nor accidential bad advice intended ;)

For the record I'll repeat on the list that the P should also note the
mailinglist archive on the matter (both the column position in a select as
well as fiddling with attnum), since the original discussion has been one
that repeated periodically.

Just to avoid anyone getting strange ideas: editting the catalog
manually has a 100% certainty of trashing any data in the table.

Well, a good chance, anyway ;)

Also various things like indexes and foreign keys may use the attnum also
and become non-functional

Absolutely true.

It's entirely possible that on an empty table with nothing attached that
changing attnum manually might work, but I wouldn't bet on it.

Me neither, since I haven't tried that in a while too (the P was already
aware of this).

Anyway, it's just a good thing people are paying attention around here ;)

Wed, 2006-01-18 at 10:10 +0100, Tino Wildenhain wrote:
Joost Kraaijeveld schrieb:
Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:

Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

Actually when I issue:

SELECT a,b,c FRM sometable; in pgadmin3 I get the columns exactly
in the order specified. Does it work differently for you? ;)
No, it does not. But the order of "select *" (or PgAdmin's "View data") differs (may differ) from
your query *and the order of attributes in my C++/Java class* and I
(sometimes) would like them to be the same without much work on my
part.

Wed, Jan 18, 2006 at 08:10:07AM +0100, Joost Kraaijeveld wrote:
Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:
Tue, Jan 17, 2006 at 10:28:03AM -0600, Tony Caduto wrote:
As long as we are talking wish lists

What I would like to see is some way to change the ordering of the
fields without having to drop and recreate the table.

Why are you asking us to optimize the 'SELECT *' case which almost
never belongs in production code in the 1st place?
Because a lot of tools that I use to manage a database during
*development* (e.g. PgAdmin) show the columns in an other order than the
order of attributes in my Java/C++ code. The "logical" order of the
columns/attributes can change during development.

Yeah, this isn't about production code, it's about making life easier on
developers. Humans naturally want to group data into natural sets, so
for example all the fields dealing with a person's address would appear
together. But if you ever have to use ALTER TABLE to add a field you're
stuck with that field being at the end of the table.

Another consideration is that the best order for people isn't the best
order for the database. For example, grouping fields of the same
alignment together will save space (and depending on the table that
savings can really start to add up).

It would definately be nice if the end-user concept of column order
wasn't tied to the physical order in the database.

Wed, Jan 18, 2006 at 04:33:23PM -0800, Stephan Szabo wrote:
Wed, 18 Jan 2006, Jim C. Nasby wrote:

Yeah, this isn't about production code, it's about making life
easier on developers. Humans naturally want to group data into
natural sets, so for example all the fields dealing with a
person's address would appear together. But if you ever have to
use ALTER TABLE to add a field you're stuck with that field being
at the end of the table.

Another consideration is that the best order for people isn't the
best order for the database. For example, grouping fields of the
same alignment together will save space (and depending on the
table that savings can really start to add up).

It would definately be nice if the end-user concept of column
order wasn't tied to the physical order in the database.

I agree with that. However, I'm not sure that an ALTER TABLE that
reorders a logical column set is necessarily the right way to handle
the issue. I think that the same path leads to realizations that a
single logical ordering may not be sufficient for development.

For example, I could see cases where say person A wants all the
address columns together but person B only cares about country and
wants the columns he deals with together in some other fashion.

Although it might be nice to have different column orderings, say
per-role, the SQL:2003 standard requires a single canonical ordering
in the information schema. How would we handle both?

Cheers,
D