Show HN: PipeSQL – Building SQL queries bottom-up using pipes and filters

Re:Show HN: PipeSQL – Building SQL queries bottom-up using p... by radiowave on Thursday October 09, 2014 @05:56PM (#8435227)

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.

[ Parent | on HN ]

Re:Show HN: PipeSQL – Building SQL queries bottom-up using p... by siddboots on Thursday October 09, 2014 @09:12PM (#8435786)

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!

[ Parent | on HN ]