This more of a general interest than specifically Postgres question. Are there any “semi-imperative” query languages that have been tried in the past? I’m imagining a language where something like this:
for employee in employees: for department in department: if employee.department == department.department anddepartment.name == "infosec": yield employee.employee, employee.name, employee.location, employee.favorite_drink
would be planned and executed like this:
SELECT employee.employee, employee.name, employee.location, employee.favorite_drink FROM employee JOIN department USING (department)WHEREdepartment.name == "infosec"
The only language I can think of that is vaguely like this is Fortress, in that it attempts to emulate pseudocode and Fortran very closely while being fundamentally a dataflow language.
Having done a lot of SQL optimisation stuff I have doubts that this is possible. The problem is that it is much easier to go from a declarative to an imperative plan than it is to go the other way. In fact sometimes we use SQL the way your first code works and then it is often a problem.
For example, consider the difference between an EXISTS and an IN query, or between an INNER JOIN and a LATERAL JOIN. PostgreSQL's optimiser is amazing at identifying cases where these are equivalent and planning accordingly, but it is extremely easy to get just outside the envelope where the optimiser gives up and has to default back to an imperative interpretation of these. Proving that two imperative approaches are equivalent is a lot harder than proving that two different imperative approaches implement the same declarative request. In other words, going imperative -> declarative strikes me as a far, far harder problem than the other way.
Also I have done a little bit of work on Apache Spark and there it is extremely important to understand the imperative side of the data flow in that case (what is partitioned and what is not).
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.