4 ms·

He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved. You can copy, paste and edi

by setr 4y ago

He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved. You can copy, paste and edit 100 times and create 100 view objects Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it. I think the usual SQL solution would be to implement a stored function and start smashing strings together

dmux 4y ago
That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!
- setr 4y ago
  That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query) Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity. Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)
zasdffaa 4y ago
So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.
- setr 4y ago
  Let’s say three usecases: 1. To Union them into multiple views 2. To instantiate individual views per query, to reuse in other queries 3. To return to client directly I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon

by setr 4y ago

dmux 4y ago

That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!

setr 4y ago
That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query) Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity. Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)

zasdffaa 4y ago

So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.

setr 4y ago
Let’s say three usecases: 1. To Union them into multiple views 2. To instantiate individual views per query, to reuse in other queries 3. To return to client directly I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon