X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8D577D1B17A for ; Thu, 5 Aug 2004 04:29:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44202-09 for ; Thu, 5 Aug 2004 07:28:55 +0000 (GMT) Received: from hotmail.com (bay7-dav12.bay7.hotmail.com [64.4.10.116]) by svr1.postgresql.org (Postfix) with ESMTP id 60F30D1B484 for ; Thu, 5 Aug 2004 04:28:51 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 5 Aug 2004 00:28:51 -0700 Received: from 217.132.39.11 by bay7-dav12.bay7.hotmail.com with DAV; Thu, 05 Aug 2004 07:28:51 +0000 X-Originating-IP: [217.132.39.11] X-Originating-Email: [ram_nathaniel@hotmail.com] X-Sender: ram_nathaniel@hotmail.com From: "Ram Nathaniel" To: Subject: SQL syntax extentions - to put postgres ahead in the race Date: Thu, 5 Aug 2004 10:28:44 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01B9_01C47AD6.FBF146A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Message-ID: X-OriginalArrivalTime: 05 Aug 2004 07:28:51.0485 (UTC) FILETIME=[DB003CD0:01C47ABD] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/34 X-Sequence-Number: 18531 This is a multi-part message in MIME format. ------=_NextPart_000_01B9_01C47AD6.FBF146A0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable Hi, I am a developer working with many databases, as a part of my job. I use he= avy SQL queries and have become somewhat of an expert in SQL, including tri= cks and workarounds of the limitation of the language. I feel like a lot of the workarounds could be avoided with adding a few new= operators to the SQL language that should be rather easy to support but wo= uld give a vast improvement and probably a leap of performance in many comm= on queries. I write you about this hoping that you would support these oper= ators (even though they are not in the ANSI) and thereby position PostGres = as a leader and not just a follower! I personaly have a great interest in s= eeing open source software making it big time! So please - forward this to = one of your more technical developers/executives and hopefully I will get t= o see it in the next version of PostGres: 1) The operator "of max": suppose I have a table "grades" of 3 fields: class/student/grade where I st= ore many grades of many students of many classes. I want to get the name of= the highest scoring student in each class. Note that there may be many stu= dents with the same grade, but for starters let's say there is a primary ke= y of class+grade. My query would now be: select student from grades where class+'#'+grade in=20 ( select class+'#'+max(grade) from grades group by class ) a This means working the query twice - and relying on louzy conversion of the= grade from numerical to textual. We could also use: select student from grades where student in=20 ( select student from grades group by class=20 having class+'#'+grade =3D max(class+'#'+grade) ) a This is even worse! The optimal would be to introduce a new operator "of max" that would be use= d as follows: select student of max(grade) from grades group by class simillarly one should support "of min" and "of any" (brings a representativ= e of the group) 2) aggregated concatenation: Traditionally the SQL language has referred from supporting order dependent= operators from taking a role in aggregated functions. This means that sinc= e the query: "select class, grade from grades" does not ensure the order of= the records returned, the operation sum() is supported (i.e. select class,= sum(grade) from grades group by class) but other operations that would be = order dependent are not supported. I think this approach should be revised. In many cases one would want to ge= t a list of the student names delimited with a comma. It would be great if = one could write: select class, list(student, ',') from grades group by class and get class list ----- ---- class1 john, ruth,... This is of course an order dependent operation so the syntax can either be: select class, list(student, ',') from grades group by class order by studen= t, grade in which case the list would be ordered before the list is created, or if n= o particular order is requested the concatenation of the names should be in= an arbitrary order. Well - that's all for now :-) Good luck! Ram ------=_NextPart_000_01B9_01C47AD6.FBF146A0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable
Hi,
 
I am a developer working with many databas= es, as a=20 part of my job. I use heavy SQL queries and have become somewhat of an expe= rt in=20 SQL, including tricks and workarounds of the limitation of the=20 language.
 
I feel like a lot of the workarounds could= be=20 avoided with adding a few new operators to the SQL language that should be= =20 rather easy to support but would give a vast improvement and probably a lea= p of=20 performance in many common queries. I write you about this hoping that you = would=20 support these operators (even though they are not in the ANSI) and thereby= =20 position PostGres as a leader and not just a follower! I personaly hav= e a=20 great interest in seeing open source software making it big time! So please= -=20 forward this to one of your more technical developers/executives and hopefu= lly I=20 will get to see it in the next version of PostGres:
 
1) The operator  "of max":
suppose= I have a=20 table "grades" of 3 fields: class/student/grade where I store many grades o= f=20 many students of many classes. I want to get the name of the highest scorin= g=20 student in each class. Note that there may be many students with the same g= rade,=20 but for starters let's say there is a primary key of class+grade.
 
My query would now be:
select student f= rom=20 grades where class+'#'+grade in
(
   select=20 class+'#'+max(grade) from grades group by class
) a
 
This means working the query twice - and r= elying on=20 louzy conversion of the grade from numerical to textual.
We could also= =20 use:
 
select student from grades where student i= n=20
(
  select student from grades group by class
  having= =20 class+'#'+grade =3D max(class+'#'+grade)
) a
 
This is even worse!
 
The optimal would be to introduce a new op= erator=20 "of max" that would be used as follows:
 
select student of max(grade) from grades g= roup by=20 class
 
simillarly one should support "of min" and= "of any"=20 (brings a representative of the group)
 
2) aggregated concatenation:
Traditiona= lly the=20 SQL language has referred from supporting order dependent operators from ta= king=20 a role in aggregated functions. This means that since the query: "select cl= ass,=20 grade from grades" does not ensure the order of the records returned, the= =20 operation sum() is supported (i.e. select class, sum(grade) from grades gro= up by=20 class) but other operations that would be order dependent are not=20 supported.
I think this approach should be revised. In many cases one wo= uld=20 want to get a list of the student names delimited with a comma. It would be= =20 great if one could write:
select class, list(student, ',') from grades g= roup=20 by class
and get
 
class  list
-----  ----
cl= ass1=20 john, ruth,...
.
.
.
 
This is of course an order dependent opera= tion so=20 the syntax can either be:
select class, list(student, ',') from grades g= roup=20 by class order by student, grade
in which case the list would be ordered= =20 before the list is created, or if no particular order is requested the=20 concatenation of the names should be in an arbitrary order.
 
Well - that's all for now :-)
 
Good luck!
 
Ram
------=_NextPart_000_01B9_01C47AD6.FBF146A0--