4 ms·

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. Som

by pg314 10y ago

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-createview.html https://www.postgresql.org/docs/current/static/sql-createvie...

jerf 10y ago
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 10y ago
  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 10y ago
  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. ;-)

by pg314 10y ago

jerf 10y ago

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 10y ago
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 10y ago
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. ;-)