Обсуждение: PostgreSQL Top 10 Wishlist

I've been using PostgreSQL 8.1 with EMS PostgreSQL Manager and PHP for
about a month now and here are the top 10 features I'd like to see.
Keep in mind that I'm a novice so we might have some of this and I just
can't find it in the docs.

1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
trigger. 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.

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.

3.
The ability to disable rules, triggers, views, functions, languages and
the like without dropping them. Maybe we have this and EMS just doesn't
impliment 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.

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!

6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. Or at least other databases on the same
server. Granted it might not be possible to JOIN, UNION 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.

7.
An XML field type and associated XPath/DOM functions. Other exotic
field types like Image might be nice for some people as well. But XML
would be awesome.

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

9.
The ability to nest fields within fields. For example:
  PERSON
     NAME
         LAST
         FIRST
     PHONE

10.
Or 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 FROM customers WHERE balance < 0;
...where balance actually performs a behind the scenes JOIN 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. Such fields can have two functions: GET and SET.
SET executes when the field is updated. If the SET procedure is not
specified updating the field could throw an exception (e.g. read only).
If SET is specfied but doesn't do anything the update would be ignored.
This effectively impliments triggers with column granularity. DELETE
and INSERT clauses could be added as well. This is really borrowing
heavily from object oriented concepts (class properties in VB are
defined like this). Now suppose we take this a step farther down the
road of rows being objects and give them private and public fields.
Public fields can be queried against from outside the table's own
virtual field functions while private fields are hidden. Public fields
can validate and normalize data before storing that data internally for
example. For example:
      In: 123 456-7890
      Out: (123) 456-7890
      Stored As:
           PHONE = (Virtual Function, with Regexp input parser)
               AREA_CODE = 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.

Just some stupid ideas.

-Robert

On 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 FUNCTION 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. Or at least other databases on the same
> server. Granted it might not be possible to JOIN, UNION 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:
>   PERSON
>      NAME
>          LAST
>          FIRST
>      PHONE

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.
> Or 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 FROM customers WHERE balance < 0;
> ...where balance actually performs a behind the scenes JOIN 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
>       Out: (123) 456-7890
>       Stored As:
>            PHONE = (Virtual Function, with Regexp input parser)
>                AREA_CODE = 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,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Jan 13, 2006, at 13:51 , rlee0001 wrote:

> I've been using PostgreSQL 8.1 with EMS PostgreSQL Manager and PHP for
> about a month now and here are the top 10 features I'd like to see.
> Keep in mind that I'm a novice so we might have some of this and I
> just
> can't find it in the docs.

There *is* a lot of documentation, but it's also quite thorough. You
might want to take some time and look through it.

http://www.postgresql.org/docs/current/interactive/index.html

Selected responses below.

> 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.

You can definitely cast boolean to integer:

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
build 5026)
(1 row)

test=# select false::boolean::integer;
int4
------
     0
(1 row)

test=# select true::boolean::integer;
int4
------
     1
(1 row)

And you can create your own casts to text if you'd like. See the docs:

http://www.postgresql.org/docs/current/interactive/sql-createcast.html

> 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.

You can dump the schema of a database using pg_dump --schema-only.
Does this do what you want?

http://www.postgresql.org/docs/current/interactive/app-pgdump.html

> 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!

Andrew Dunstan has developed EnumKit to allow you to have enumerated
data types in PostgreSQL. Hopefully this can help you.

http://www.oreillynet.com/pub/a/databases/2006/01/06/enumerated-
fields-in-postgresql.html

> 6.
> Cross database queries. I'd like to be able to query a MS SQL Server
> database from within PL/PGSQL. Or at least other databases on the same
> server. Granted it might not be possible to JOIN, UNION 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.

While PL/pgsql won't let you do this, you can probably do some things
with pl/perlu or some of the other untrusted languages. Also, dbi-
link may help you as well.

http://pgfoundry.org/projects/dbi-link/

> 9.
> The ability to nest fields within fields. For example:
>   PERSON
>      NAME
>          LAST
>          FIRST
>      PHONE

Have you looked at composite types?
http://www.postgresql.org/docs/current/interactive/sql-createtype.html


>
> 10.
> Or 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 FROM customers WHERE balance < 0;
> ...where balance actually performs a behind the scenes JOIN 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 can update a view if you create rules to do so:

http://www.postgresql.org/docs/current/interactive/rules.html

> This is really borrowing
> heavily from object oriented concepts (class properties in VB are
> defined like this).

While there are some similarities between classes and tables, and
objects and rows, they're not the same thing. Some of the things you
describe can be done using user-defined functions, while others are
probably better done in your application. You can also create your
own datatypes that give you all the functionality you want:
PostgreSQL is *very* extensible.

Hope this helps a bit.

Michael Glaesemann
grzm myrealbox com

rlee0001 wrote:

> 2.
> The ability to typecast from boolean to other datatypes. For example:
>         false::varchar
> ...would return varchar 'false' while:

Why should it return 'false'?  If anything, it seems to me it should do
the same as this:

   # select false;
    bool
   ------
    f
   (1 row)

That is, false::varchar => 'f'.

- John D. Burger
   MITRE

On Jan 13, 2006, at 8:47 AM, Michael Glaesemann wrote:

> You can definitely cast boolean to integer:

The poster mentioned using PostgreSQL/PHP which may be the real
source of the issue. Boolean values are returned to PHP as strings
't' and 'f'. Of course, 'f' is not equivalent to FALSE in PHP. It
would be really nice if the PHP module returned a false value instead
of 'f'.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

>
>> 10.
>> Or 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 FROM customers WHERE balance < 0;
>> ...where balance actually performs a behind the scenes JOIN 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.

--
Aly S.P Dharshi
aly.dharshi@telus.net

     "A good speech is like a good dress
      that's short enough to be interesting
      and long enough to cover the subject"

>> 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.
>


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On 1/13/06, Aly Dharshi <aly.dharshi@telus.net> wrote:
>
> >
> >> 10.
> >> Or 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 FROM customers WHERE balance < 0;
> >> ...where balance actually performs a behind the scenes JOIN 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.
>
> --
> Aly S.P Dharshi
> aly.dharshi@telus.net
>

There's some work on making views updatable, but even if the patch get
ready and accepted it will be just for simple views (just one table,
at least for the first release) for complex queries you still have to
write the rules...

The poster mentioned he want a column that "behinds the scene" do some
calculations, and that it's obviously a function as a column of the
view... and there is no chance that that column will be updatable
(rules created manually nor automatic)

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Robert,

Others have covered some of your topics.

On Thu, 12 Jan 2006 20:51:44 -0800, rlee0001 wrote:

> 7.
> An XML field type and associated XPath/DOM functions. Other exotic
> field types like Image might be nice for some people as well. But XML
> would be awesome.
>

The contrib module xml2 (in the core source distribution and also
built in some bianry versions) provides some support functions for XPath,
where you store the documents in columns of type text. There is also a
project, XpSQL that does decomposition of XML documents - see
http://gborg.postgresql.org/project/xpsql/projdisplay.php

> Just some stupid ideas.

It's always valuable to see what people are interested in - but there are
sometimes reasons why it's not seen as practical or appropriate (standards
compliance, namespace pollution etc.) to include a requested feature.

Regards

John

On Fri, Jan 13, 2006 at 02:23:29PM +0100, Martijn van Oosterhout wrote:
> On 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 TODO :)

> > 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. Or at least other databases
> > on the same server. Granted it might not be possible to JOIN,
> > UNION 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:
> >   PERSON
> >      NAME
> >          LAST
> >          FIRST
> >      PHONE
>
> 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 TODO 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
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

Martijn van Oosterhout wrote:
> On 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 FUNCTION 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. Or at least other databases on the same
> > server. Granted it might not be possible to JOIN, UNION 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:
> >   PERSON
> >      NAME
> >          LAST
> >          FIRST
> >      PHONE
>
> 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.
> > Or 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 FROM customers WHERE balance < 0;
> > ...where balance actually performs a behind the scenes JOIN 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
> >       Out: (123) 456-7890
> >       Stored As:
> >            PHONE = (Virtual Function, with Regexp input parser)
> >                AREA_CODE = 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,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

"rlee0001" <robeddielee@hotmail.com> writes:
>> > example. For example:
>> >       In: 123 456-7890
>> >       Out: (123) 456-7890
>> >       Stored As:
>> >            PHONE = (Virtual Function, with Regexp input parser)
>> >                AREA_CODE = 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.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
"It is easier  to optimize correct code, than  correct optimized code"
-- Yves Deville

On 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?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

jnasby@pervasive.com ("Jim C. Nasby") writes:
> On 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.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/linux.html
Rules of the Evil Overlord #35. "I  will not grow a goatee. In the old
days they made  you look diabolic. Now they just make  you look like a
disaffected member of Generation X." <http://www.eviloverlord.com/>

On 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?

> unless...is 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
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

> > > 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;');

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On 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...

Or maybe not; I suspect most people just punt on phone numbers, or force
them to a very strict format.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

jnasby@pervasive.com ("Jim C. Nasby") writes:
> On 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...
>
> Or 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 :-(.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/wp.html
Oh,  boy, virtual memory!  Now I'm  gonna make  myself a  really *big*
RAMdisk!

On Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:
> On 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.


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Op 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.


--


With kind regards,
Met vriendelijke groet,




ir. F.T.M. van Vugt bc.
Technisch Directeur (CTO)
Foxi Consultants in Technology BV
===================================
Ringbaan Oost 102-008
5013 CD  Tilburg
Tel: (+31).13.5425551, Fax: (+31).13.5452087
===================================
Would you buy a car with the hood welded shut? (Linux, the maintainable OS)
This is Unix Country. On a quiet night you can hear NT reboot.
===========================================
De in dit emailbericht verstrekte informatie is vertrouwelijk en uitsluitend
bestemd voor de geadresseerde. Het kopiëren van of verstrekken aan en gebruik
door derden van deze informatie is niet toegestaan. Door de elektronische
verzending van dit bericht kunnen er geen rechten worden ontleend aan de
informatie.
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Copyright (c) 2006 Foxi C.i.T. BV All world wide rights reserved.
===========================================

L.S.

Sorry 'bout that last post in dutch, it was meant to go to a private
address.....




--
Best,




Frank.

On Wed, Jan 18, 2006 at 09:10:23AM +0100, ir. F.T.M. van Vugt bc. wrote:
> Op 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.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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

On Wed, 2006-01-18 at 09:10 +0100, ir. F.T.M. van Vugt bc. wrote:
> Op 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.

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

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 OP 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 OP 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 OP was already
aware of this).

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



--
Best,




Frank.

Joost Kraaijeveld schrieb:
> On 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 FROM sometable; in pgadmin3 I get the columns exactly
in the order specified. Does it work differently for you? ;)

On Wed, 2006-01-18 at 10:10 +0100, Tino Wildenhain wrote:
> Joost Kraaijeveld schrieb:
> > On 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 FROM 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.


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

On Wed, Jan 18, 2006 at 08:10:07AM +0100, Joost Kraaijeveld wrote:
> On Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:
> > On 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.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Wed, 18 Jan 2006, Jim C. Nasby wrote:

> On Wed, Jan 18, 2006 at 08:10:07AM +0100, Joost Kraaijeveld wrote:
> > On Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote:
> > > On 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.

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.

On Jan 19, 2006, at 9:10 , Jim C. Nasby wrote:

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

Tom Lane has mentioned at least a couple of times that decoupling the
(SQL-required) logical order from the physical order is probably
pretty hairy and would lead to easy to make and hard to track down
bugs and I don't doubt that it's pretty detailed and complicated
work. If someone were so inclined, they could probably look into this
more closely and see what it would take to create a clean, easy-to-
use and easy-to-maintain interface between the physical and logical
data representation (including column order).

Michael Glaesemann
grzm myrealbox com

On Wed, Jan 18, 2006 at 04:33:23PM -0800, Stephan Szabo wrote:
> On 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
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

David Fetter <david@fetter.org> writes:
> 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?

If you want some other column ordering, you make a view.  The only thing
we're really lacking to make that a fully acceptable answer is
automatically-updatable views, which I believe someone is working on...

            regards, tom lane

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

Ok this may be over simplistic, but we have a attnum for the physical
order, why not just add a attorder and then we could just change that
numbering and order by that field instead of attnum?

That is exactly how Firebird does it.

I first started to develop FB Lightning Admin and when I discovered
Postgresql I immediately switched :-), so that's how I know how Firebird
does things.

The whole re ordering thing is purely for organization are readability,
but it's something that is asked for by many peope using PG admin tools.

It can be done now, but it's a huge PITA...the whole creating a new
table with the right order, copying the data to the new table,
recreating all contrainsts,indexes,triggers etc,dropping the original
table etc makes me shudder..

Later,

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:
>> It would definately be nice if the end-user concept of column order
>> wasn't tied to the physical order in the database.

> Ok this may be over simplistic, but we have a attnum for the physical
> order, why not just add a attorder and then we could just change that
> numbering and order by that field instead of attnum?

Please go back and read the very extensive discussions of this question
a couple years ago in the pghackers archives (around the time we were
implementing DROP COLUMN, iirc).  There is no "just" about it.

            regards, tom lane

On Wed, 18 Jan 2006, David Fetter wrote:

> On Wed, Jan 18, 2006 at 04:33:23PM -0800, Stephan Szabo wrote:
> > On 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?

That's part of the problem.

I'm not 100% sure where the people who want this are going with the
feature.  I've heard a lot of talk about tools, but that's potentially
solvable without changing the canonical ordering as long as the tools obey
not directly using select * internally (if the user asks for it as an sql
statement obviously that's what should get done); and it was done in a
postgresql specific, but documented way, to allow different tools to use
it.  If the issue then is that select * doesn't come back in that order,
then we get back into the question of does a single ordering make sense?
If not, and we allow select * to change, then applications and queries
will fail (imagine select * from a union select * from b where different
users are reordering a and b separately).

Tom Lane wrote:

> Please go back and read the very extensive discussions of this question
> a couple years ago in the pghackers archives (around the time we were
> implementing DROP COLUMN, iirc).  There is no "just" about it.

Maybe we need a easy button like in those Staples commercials :-)

So it's a real big deal to add another column to pg_attribute and
populate it once with the same value as attnum when a column is added to
a table?  The attorder wouldn't really have to do anything except allow
the developer to sort on that instead of attnum, and then we could just
change the values in attorder to get the visual ordering we want.

I understand everyone wants a big fancy elegant solution, but maybe
something simple is all that is needed.

I am not a C or C++ developer and I don't intend to take it up anytime
soon, so I can't really comment on what it would really "just" take, but
hey we are just talking here right?


--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

On Sat, 21 Jan 2006, Tony Caduto wrote:

> Tom Lane wrote:
>
> > Please go back and read the very extensive discussions of this question
> > a couple years ago in the pghackers archives (around the time we were
> > implementing DROP COLUMN, iirc).  There is no "just" about it.
>
> So it's a real big deal to add another column to pg_attribute and
> populate it once with the same value as attnum when a column is added to
> a table?  The attorder wouldn't really have to do anything except allow
> the developer to sort on that instead of attnum, and then we could just
> change the values in attorder to get the visual ordering we want.

If the effect you're looking for is entirely visual reordering with no
logical effects, then the server really doesn't need to be involved at
that level.

However, that's a very weak definition of column reordering because it
potentially hides the actual order used in those few places that SQL
cares.  I think a client that tells me the columns are a, b, c but then
gives me an error on "insert into table values (aval, bval, cval)" because
the actual logical order is different is probably fairly broken.

  I think a client that tells me the columns are a, b, c but then
> gives me an error on "insert into table values (aval, bval, cval)" because
> the actual logical order is different is probably fairly broken.

I guess that could be a problem, I was thinking in terms of how I do a
insert, I rarely do it that way, I usually specify the columns by name i.e.
insert into blabla (fieldname1,fieldname2) values('bla','bla')

oh well, there goes that idea :-)


--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

Tony Caduto schrieb:
>  I think a client that tells me the columns are a, b, c but then
>
>> gives me an error on "insert into table values (aval, bval, cval)"
>> because
>> the actual logical order is different is probably fairly broken.
>
>
> I guess that could be a problem, I was thinking in terms of how I do a
> insert, I rarely do it that way, I usually specify the columns by name i.e.
> insert into blabla (fieldname1,fieldname2) values('bla','bla')
>
> oh well, there goes that idea :-)

Maybe you can save it if you "mis"-use the column comments and
make your gui-client oder on: attnum or column-name or comment.
And if you have a table where you still cannot find the column
you want, there is a strong evidence for redesign I'd say ;)

Regards
Tino