5 ms·

+1 sql is extremely elegant composable and is under rated Postgres is very powerful. While I sought a short detour in nosql Mongodb land now back to Mysql Post

• naasking 4y ago
  SQL has some positives but it is not composable. At all. This is because relations are not first-class values in SQL.
  • dventimi 4y ago
    Is a query not a relation?
    • naasking 4y ago
      Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate < '2000-01-01'
      • noloblo 4y ago
        Exactly since it declarative the style Lends itself using stored procedure calls to become composable
        • naasking 4y ago
          You cannot abstract over stored procedures either, so that's still not composable.
          • dventimi 4y ago
            No idea what this means
            • naasking 4y ago
              Think of first-class functions. Can't do that with stored procedures, just like you can't do that with queries or tables in SQL.
        • alphanumeric0 4y ago
          Furthermore, stored procedures/functions are not queries.
          • dventimi 4y ago
            Stored procedures can be relations. Queries are relations. Ergo, stored procedures can be queries.
      • Lemaxoxo 4y ago
        Isn't that the point of common table expressions (CTEs)?
        • naasking 4y ago
          See: https://news.ycombinator.com/item?id=35058927 https://news.ycombinator.com/item?id=35058927
      • datan3rd 4y ago
        with persons as (select * from Person) select Name from persons where Birthdate < '2000-01-01
        • naasking 4y ago
          Where is the assignment to a variable? Where can you construct a query using a variable in table/query position? That's the whole point of being first class and composable, a query becomes like any other value so you should be able to parameterize any query by another query assigned to a variable that may have been set inside an if-statement, or accepted as a parameter to a stored procedure. You know, the same kinds of composition we see in ordinary programming languages.
          • chrstr 4y ago
            create table x as (select * from person); select name from x where ...; there you go, just configure your editor to display "create table x" as "declare x = " ;) or even a version with lazy evaluation: create view x as (select * from person); select name from x where ...;
            • alphanumeric0 4y ago
              What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?
              • dventimi 4y ago
                If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.
                • alphanumeric0 4y ago
                  That's a good point. This would rule out SQLite for me.
      • Foobar8568 4y ago
        You can do composition through CTE or Table-Valued Function.
        • naasking 4y ago
          CTE and TVF still treat tables as second class citizens, so while they enable some forms of composition, they're still very restricted. This has been the consistent story with SQL, 15 ways to use queries and tables, all to work around the limitation that they are not first class values.
  • simonw 4y ago
    CTEs go a long way to making SQL more composable.
• noloblo 4y ago
  Postgres has read write parallism that can scale across millions of read writes ; if ml model is inherent in the Postgres db it is indeed very elegant reminds me of the glory days of tsearch2 to do text search in Postgres for searching our internal document repository using nothing but Postgres and boy was it faster than Java based search systems