PostgreSQL Top 10 Wishlist

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/

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

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

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!

"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
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/&gt;

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