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

Kevin Walker wrote:
> Yes, the preference clause can be rewritten using standard SQL.  The syntax to duplicate the example result set is
listedbelow.  The syntax is not very flexible or easy to read.   
 
> 
> select id 
> from computer
> where (main_memory = (select max(main_memory) 
>                       from computer)
>        and cpu_speed = (select max(cpu_speed) 
>                         from computer
>                         where cpu_speed < (select max(cpu_speed) from computer)))
>    or (cpu_speed = (select max(cpu_speed) 
>                         from computer)
>        and   main_memory = (select max(main_memory) 
>                         from computer
>                          where main_memory < (select max(main_memory) from computer)))

Well, that's not 100% correct, but the idea is something like this. In 
particular, if you'd have only one entry in the table, then this query 
would not return any rows, which would be wrong. Also, if you had a 
computer that has larger main memory and a faster CPU than any other 
copmuter, it should be returned as the result, but the above query would 
fail to do that.

The point is not rewriting that particular preference query into a 
standard query. The point is whether it's worth having an automated 
mechanism for executing arbitrary preference queries with complex 
preferences (again: the syntax is richer, I didn't want to go into any 
detail about it before getting some feedback).

Let me give you a more sophisticated example.
You have a webpage that sells used cars. You have your typical search 
form with car make, colour, engine power and so on. Normally, you would 
make the search form input fields correspond to SQL WHERE clauses. So, 
if I want a white Honda with a 180 hp engine and about 40k kilometers of 
mileage I enter these parameters and hit the submit button. Now imagine 
I don't get any results for my search. That could mean that you have no 
Honda cars in stock, but it can also mean that you have my perfect Honda 
at a bargain price, it's only that it's black. Or maybe you have a 
Honda, but it has a 160 hp engine? Or is it just that the one perfect 
Honda you have has a mileage of just over 41k km, and that's why I 
didnt' get it in my result set? People seldom want a perfect match when 
they are searching for something. They want the best match, they can 
get. So, if I wanted to get a Honda with a decent engine and my 
favourite color is white, I'd say:

SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND 
mileage AROUND 40000) CASCADE color = 'white';

Remember, that an AND in a preference clause constructs a partial order. 
The query says: I equally prefer having a 180 hp engine and having a car 
that has a mileage of 40k km. Tha CASCADE clause intrudoces a less 
important preference. It means that the color is not as important to me 
as power and mileage, but if I had a choice I'd take the white one.

I'd strongly recommend skimming through the paper I mentioned in my 
first email, it explains stuff much better than I do.

Preference SQL in Postgres could, as I see it, become one of the 
distinct features that no other widespread database system has and that 
could potentially be massively useful for online shops, social networks, 
etc. - you name it.
After hearing Bruce's keynote at PGCon and how Postgres now should be 
aiming at more that just catching up with the big guys I just thought: 
well, that's one neat feature that none of them has, that's useful, 
that's kind of sexy *and* I could get my degree out of it.

Cheers,
Jan

-- 
Jan Urbanski
GPG key ID: E583D7D2

ouden estin