I'd recommend reading the PGSQL manual, they go very in depth about many of the supported features and how they are implement and can be used.
That's kind of orthogonal to what I think is the issue being expressed here.
SQL can do many things; the problems tend to be when a query doesn't perform consistently and predictably. There's always a balance to be struck between communicating what is to be done, and how it is to be done, and SQL leaves so much of the "how" out that the query interpreter/optimizer is incredibly sophisticated and does a fantastic amount of work and yet frequently gets things spectacularly wrong, maybe due to misconfiguration and maybe due to fundamental limitations.
Obviously more information on how to do something is not always better; otherwise we'd be using assembler. But there is a balance.
Experts tend to say "write everything in one query, and if it doesn't work, fix the configuration of your database" which is not helpful given the division of responsibilities in any company. But they will say that because they are devoted to the idea that all that expressiveness is good for something.
If that were true, people would build RDBMS’s in SQL.
It fulfils its purpose and does it well.
Since the addition of SQL/PSM (1996 IIRC) it has become a computationally complete language (procedural like many of the others) with variables and loops and what have you.
Sounds like you're looking for a magic silver bullet - there's no free lunch in our field though.
Lastly, the comment I was replying to can be paraphrased as "well is SQL is so great why aren't RDBMS' built using SQL, huh?". Which is a ridiculous question since SQL isn't the right tool for that job - its very name tells you that.
If you want to continue arguing against strawmen do it with someone else.
Perhaps SQL is fine if it's your interface for accessing data on a one-off basis, but if you're trying to build a complex tool on top of it (say, an analysis tool for arbitrary data), the inconsistencies and performance concerns mount. People often end up inventing their own proprietary databases to do these analyses (e.g., virtually any business intelligence tool) assuming they can afford to do so. Perhaps EdgeQL isn't the ideal alternative, but as it is SQL is not good enough for many use cases.