Re: proposal: Preference SQL - Mailing list pgsql-hackers

<div style="text-align: left;">Jan,<br /><br />;-)  ... agree with your points.  Note I wasn't trying to duplicate the
fullfunctionality of the preferences clause.  ...just pointing out that the example presented could be duplicated with
standardSQL with the result being both ugly and confusing.  <br /><br />My intent was show that the syntax you proposed
waseasier to read and more flexible. <br /><br />Kevin<br /></div><br /><br /><br /><hr id="stopSpelling" />> Date:
Sat,31 May 2008 16:53:54 +0200<br />> From: j.urbanski@students.mimuw.edu.pl<br />> To:
skywalkereast@hotmail.com<br/>> CC: srb@cuci.nl; pgsql-hackers@postgresql.org<br />> Subject: Re: [HACKERS]
proposal:Preference SQL<br />> <br />> Kevin Walker wrote:<br />> > Yes, the preference clause can be
rewrittenusing standard SQL. The syntax to duplicate the example result set is listed below. The syntax is not very
flexibleor easy to read. <br />> > <br />> > se lect id <br />> > from computer<br />> > where
(main_memory= (select max(main_memory) <br />> > from computer)<br />> > and cpu_speed = (select
max(cpu_speed)<br />> > from computer<br />> > where cpu_speed < (select max(cpu_speed) from
computer)))<br/>> > or (cpu_speed = (select max(cpu_speed) <br />> > from computer)<br />> > and
main_memory= (select max(main_memory) <br />> > from computer<br />> > where main_memory < (select
max(main_memory)from computer)))<br />> <br />> Well, that's not 100% correct, but the idea is something like
this.In <br />> particular, if you'd have only one entry in the table, then this query <br />> would not return
anyrows, which would be wrong. Also, if you had a <br />> computer that has larger main memory and a faster CP U
thanany other <br />> copmuter, it should be returned as the result, but the above query would <br />> fail to do
that.<br/>> <br />> The point is not rewriting that particular preference query into a <br />> standard query.
Thepoint is whether it's worth having an automated <br />> mechanism for executing arbitrary preference queries with
complex<br />> preferences (again: the syntax is richer, I didn't want to go into any <br />> detail about it
beforegetting some feedback).<br />> <br />> Let me give you a more sophisticated example.<br />> You have a
webpagethat sells used cars. You have your typical search <br />> form with car make, colour, engine power and so
on.Normally, you would <br />> make the search form input fields correspond to SQL WHERE clauses. So, <br />> if
Iwant a white Honda with a 180 hp engine and about 40k kilometers of <br />> mileage I enter these parameters and
hitthe submit button. Now imagine <br />> I don't get any re sults for my search. That could mean that you have no
<br/>> Honda cars in stock, but it can also mean that you have my perfect Honda <br />> at a bargain price, it's
onlythat it's black. Or maybe you have a <br />> Honda, but it has a 160 hp engine? Or is it just that the one
perfect<br />> Honda you have has a mileage of just over 41k km, and that's why I <br />> didnt' get it in my
resultset? People seldom want a perfect match when <br />> they are searching for something. They want the best
match,they can <br />> get. So, if I wanted to get a Honda with a decent engine and my <br />> favourite color is
white,I'd say:<br />> <br />> SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND <br />>
mileageAROUND 40000) CASCADE color = 'white';<br />> <br />> Remember, that an AND in a preference clause
constructsa partial order. <br />> The query says: I equally prefer having a 180 hp engine and having a car <br
/>>that has a mileage of 40k km. Tha CASCADE clause intrudoces a less <br />> important preference. It means that
thecolor is not as important to me <br />> as power and mileage, but if I had a choice I'd take the white one.<br
/>><br />> I'd strongly recommend skimming through the paper I mentioned in my <br />> first email, it
explainsstuff much better than I do.<br />> <br />> Preference SQL in Postgres could, as I see it, become one of
the<br />> distinct features that no other widespread database system has and that <br />> could potentially be
massivelyuseful for online shops, social networks, <br />> etc. - you name it.<br />> After hearing Bruce's
keynoteat PGCon and how Postgres now should be <br />> aiming at more that just catching up with the big guys I just
thought:<br />> well, that's one neat feature that none of them has, that's useful, <br />> that's kind of sexy
*and*I could get my degree out of it.<br />> <br />> Cheers,<br />> Jan<br />> <br />> -- <br /> >
JanUrbanski<br />> GPG key ID: E583D7D2<br />> <br />> ouden estin<br /><br /><hr />E-mail for the greater
good.<a href="http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ GreaterGood" target="_new">Join the i'm
Initiativefrom Microsoft.</a>