[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. ;-)