Re: [GENERAL] PostgreSQL Top 10 Wishlist
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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
Tony Caduto <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Top 10 Wishlist
> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
David Fetter <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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? ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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. > 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 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. signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL Top 10 Wishlist
L.S. Sorry 'bout that last post in dutch, it was meant to go to a private address. -- Best, Frank. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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. === ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
[EMAIL PROTECTED] ("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!
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
> > > 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 ;)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Top 10 Wishlist
[EMAIL PROTECTED] ("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."
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Top 10 Wishlist
"rlee0001" <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
On 1/13/06, Aly Dharshi <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > 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 ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 [EMAIL PROTECTED] "A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject" ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 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 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. signature.asc Description: Digital signature
