[jerf]

Powerful sorta, fun debateably, highly rewarding probably, flexible, absolutely not. By far one of the biggest reasons to use something other than raw SQL is because of SQL's near total lack of composability. Show me a valid SQL fragment that represents "the 'permission' field of the given table is set to true OR the joined-in user has the superuser flag", where the join is represented in the fragment itself such that any query I apply it to will have the requisite join added in if necessary. Then show me a fragment that represents "this email and all of its attachments". Then show me how to compose those fragments into a full query. You can't; if you're using raw SQL you have to manually interleave those things into every query you want to use them in. SQL basically mandates copy and paste as the only abstraction available, and it isn't even all that great at that since there's no such thing as scopes within a query making it easy for your copy & pasted fragments to stomp on each other's names.

Some of the additional query languages might be able to do it (they could certainly do chunks of what I said), but they'd still be pretty klunky about it since they bodge it on the side, and still don't compose anywhere near as well as they could or should.

Mind you, I'm not sure this library can do it either; SQL is also quite difficult to wrap around because of its structural deficiencies. Trying to hack away foundational issues at higher levels is always messy, error-prone, and still filled with the quirks that shine through.

[pg314]

You can use VIEWs [1] as an abstraction mechanism. It's not perfect, but it can abstract certain things away. I think it would work for your first example.

Something like:

  CREATE VIEW permitted_X AS
  SELECT X.*
  FROM X, user
  WHERE X.user_id = user.user_id
        AND (X.permission OR user.is_super);

You second example doesn't have enough info for me to see if it can be accomplished by a VIEW.

Other abstraction mechanisms are user-defined functions.

[1] https://www.postgresql.org/docs/current/static/sql-createvie...

[jerf]

With your view I still think I can't pass a table in; your X is not a variable. That's my point. There's a lot of power in SQL and its associated technologies, but there's also a loooooot of places where in a modern system we'd have a variable or some other composition mechanism, but there's no first-class existence of that mechanism in SQL. For instance, in systems programming languages, "first-class functions" have won so thoroughly that it's hardly even a "feature" anymore, it's just part of the baseline for any new language, or it better have a darned good reason they aren't present. SQL has not even remotely had that revolution.

Some of these things are fixed up by the product-specific languages, but only some of these things.

Here's another example; using your product-specific language, can you create a table for me with a variable number and types of columns based on the parameters passed in? I don't know them all, but I bet it's hard in most or all of them. No credit if your product-specific language lets you bash a string together and then somehow execute it; I'm calling for everything to be done via first-class mechanisms. (Also, I'm not asking for whether this is a good idea; it is obviously a tricky thing of dubious use. But that should be a software engineering determination, not a language restriction.)

[tkyjonathan]

If you want the table as a variable, you would need to use a prepared statement:

PREPARE stmt1 FROM 'SELECT X.* FROM ? X, user WHERE X.user_id = user.user_id AND (X.permission OR user.is_super)';

EXECUTE stmt1 USING 'yourtable';

[commentzorro]

As they've stated, the authors of SQL are not opposed to generics. They just haven't found an idiomatic way of incorporating them into the language that doesn't also impact prepare times. ;-)

[eternalban]

I thought the unit object in conceptual model of SQL was relation/table. So a valid "SQL fragment" is a expression that evaluates to a result table, i.e a subquery. And in something like Postgres, you have functions returning tables, which should address the scoping issue that you raise.

[jsudhams]

When I run sql profiler for performance issues I like to see what is pass is what is received by DBEngine, I am not sure if it is helpful to add another layer for simple crud apps which is more than 85% in real world.

[sirtaj]

Not to argue your general point, but it seems to me that your specific composability examples could be addressed with PostgreSQL's temporary views and WITH clause.