There's quite few articles about it on depesz.com. And since I started
to blog recently about my own skirmishes/encounters with postgresql
too - I'll probably write something about it.
So far, personally - I used WITH() for PI calculations, as an example.