And also in Postgres. But once you've got queries that run to about 10 WITH clauses chained one after the other, just because you can't calculate a value in the SELECT clause and then refer to it from within the same statement, you tend to start wishing there was a better way to do it.
As useful as SQL is in so many ways, as a piece of language design it's a total bloody travesty. I'm delighted to see any movement towards a query language that keeps the nice declarative dataflow property that complex SQL queries can have, but provides better means of abstraction, and the possibility to write more intention-revealing code.
Postgres implement WITH in a way that doesn't lend itself to modularity. When you declare a subquery in a WITH block, the query optimiser won't attempt to integrate your query over that boundary. In other words, your entire subquery will be materialised before the lower query is run.
In Oracle's implementation WITH is more like a VIEW.
Edit: BTW, this is not an accident or oversight on the part of Postgres. This is in the spec!
I did quite a bit of SQL in my last job, and I can't think of a single case where I wish I had a pipeline operator. If I wanted to do something similar to the example on your homepage the simplest way is just to use subqueries.
FROM (SELECT TOP 10 T1.OrderID, T1.ORDSUM
FROM
(SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS SaleAmount
FROM "Order Details"
GROUP BY OrderID) AS T1
ORDER BY T1.ORDSUM DESC) AS OD
LEFT JOIN Orders AS ORD ON OD.OrderID = ORD.OrderID
LEFT JOIN Employees AS EMP ON ORD.EmployeeID = EMP.EmployeeID
That's fairly concise, and was quick to bash out. I'd wager it could be made even smaller if I used LINQ instead of SQL. Perhaps your method has some advantages in other types of query? How do you see it?
This is an example where SQL can be written in multiple ways to get the same result. It doesn't matter how many lines SQL contains since its all depends on how the database engine going to execute it. Therefore, pipeSQL's main aim is to make it easier to read and understand the query after couple of days/weeks :) .
That's what comments are for. Plus, I didn't find the PIPESQL syntax particularly easy to parse, of course that's natural for a new language, but in order to encourage adoption it'd be best for you to look to simplify it further.
As useful as SQL is in so many ways, as a piece of language design it's a total bloody travesty. I'm delighted to see any movement towards a query language that keeps the nice declarative dataflow property that complex SQL queries can have, but provides better means of abstraction, and the possibility to write more intention-revealing code.