Thread
-
Wishlist for next version: group by clause
Edmund Mergl <e.mergl@bawue.de> — 1998-06-13T18:07:42Z
Hi, currently I'm writing a Web application, which should be able to interface to any SQL database. Using perl this seems to be straigt forward, mainly due to the DBI module of Tim Bunce. What makes this task really difficult are the SQL-dialects of every database. Although SQL is standardized, there are many, subtle differences which have to be taken into account. After stripping down my application to an absolut basic syntax, there is still one problem left. PostgreSQL understands the following syntax: select count(SUBSTR(var,1,5)), SUBSTR(var,1,5) from t group by 2; select count(SUBSTR(var,1,5)) as x, SUBSTR(var,1,5) as y from t group by y; Unfortunately other databases - like Oracle - are not able to handle these statements. Oracle understands only the following syntax: select count(SUBSTR(var,1,5)), SUBSTR(var,1,5) from t group by SUBSTR(var,1,5); which gives an error with PostgreSQL ! I don't know if any of these variants are standard or non-standard, but it would be very helpful, if PostgreSQL would be able to handle all of these examples. From the functional point of view, there is no difference. I guess, only the parser has to be adapted. Edmund -- Edmund Mergl mailto:E.Mergl@bawue.de Im Haldenhau 9 http://www.bawue.de/~mergl 70565 Stuttgart fon: +49 711 747503 Germany
-
Re: [HACKERS] Wishlist for next version: group by clause
De Clarke <de@ucolick.org> — 1998-06-16T18:33:39Z
I didn't realize PG could not do group by [function on column] Ouch! I *think* all the "real" RDBMS can do this. If Oracle and Sybase both support it, that makes it more or less a de facto standard :-) I'm sure we use this syntax in several places in our apps -- certainly in our time-series analysis package. Implication is that 6.3 is still not functional enough to replace an existing commercial SQL server such as Oracle or Sybase for production apps, without expensive manual proofing and rewriting of embedded SQL statements. Does anyone know whether this group by syntax is ANSI SQL92? --------------- There must be many sites in the same boat with mine: running an outmoded version of one of the Big Guys' engines, unwilling to pay the outrageous support and upgrade fees required to get current, wanting full Linux support, yet unable to switch to PG because of small gotchas like this one. It's a small gotcha if you are writing a brand new app, but it's a large gotcha if you have to comb through thousands of embedded SQL statements in hundreds of production apps and manually fix it in each instance. Is there a list of the "PG is different" gotchas like this, and is their elimination being given a high priority? I think "plug-n-play" replacement of existing servers with PG is a good practical goal -- so long as the app writers have wisely avoided vendor-specific syntax in their SQL, of course :-) I think conversions of this sort would be good publicity for PG, and I would be willing to write up a public report on mine if and when PG evolves to the point where I can do it! What do you all think about the PR value of new PG-driven apps vs conversion of existing production apps? de ............................................................................. :De Clarke, Software Engineer UCO/Lick Observatory, UCSC: :Mail: de@ucolick.org | "There is no problem in computer science that cannot: :Web: www.ucolick.org | be solved by another level of indirection" --J.O. :
-
Re: [HACKERS] Wishlist for next version: group by clause
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-06-16T19:09:41Z
> > > I didn't realize PG could not do > > group by [function on column] > > Ouch! > > I *think* all the "real" RDBMS can do this. If Oracle and > Sybase both support it, that makes it more or less a de facto > standard :-) I'm sure we use this syntax in several places in > our apps -- certainly in our time-series analysis package. > Implication is that 6.3 is still not functional enough to > replace an existing commercial SQL server such as Oracle or > Sybase for production apps, without expensive manual proofing > and rewriting of embedded SQL statements. > > Does anyone know whether this group by syntax is ANSI SQL92? Added to TODO. Vadim may have a comment on this, and how hard it is to do. I know we allow functional indexes, but am not sure how that relates to this problem. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
-
Re: [HACKERS] Wishlist for next version: group by clause
David Hartwig <daveh@insightdist.com> — 1998-06-16T20:27:35Z
Do you mean in a select statement? Such as: SELECT func(date) as month, count(*) FROM foo GROUP BY month; Or even: SELECT count(*) FROM foo GROUP BY func(date); The first is supported. The second would require some changes to the parser. De Clarke wrote: > I didn't realize PG could not do > > group by [function on column] > > Ouch! > -
group by : syntactic example (sybase)
De Clarke <de@ucolick.org> — 1998-06-16T21:25:18Z
David Hartwig said: >> Do you mean in a select statement? Such as: >> >> SELECT func(date) as month, count(*) FROM foo GROUP BY month; >> >> Or even: >> >> SELECT count(*) FROM foo GROUP BY func(date); >> >> The first is supported. The second would require some changes to the parser. #2 was what I had in mind... This is a pointless query, but it demonstrates a couple of things that the sybase SQL interpreter supports: select avg(datepart(minute,date)) from hires_events group by datepart(hour,date) 1. you can apply stat functions such as avg and sum to functions on columns as well as to raw columns 2. you can group by a function on a column I think Oracle will do this also... de ............................................................................. :De Clarke, Software Engineer UCO/Lick Observatory, UCSC: :Mail: de@ucolick.org | "There is no problem in computer science that cannot: :Web: www.ucolick.org | be solved by another level of indirection" --J.O. :