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!

This is not a replacement for SQL. Its a tool to create SQL in a different angle. An angle which is similar to UNIX pipeline concept.

Yes, but what are the advantages over SQL?

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.

Here's a rewrite of your query in T-SQL...

------------------------------------------------------

SELECT EMP.Country, EMP.LastName, EMP.FirstName, OD.OrderID, ORD.ShippedDate, OD.SaleAmount

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.