Earlier quoted context omitted.
He said that they "originally developed for MS SQL." and then "Later we added in support for Postgres/MySQL." Sounds like they develop a product that can use multiple RDBMS back-ends. Also, I'm guessing that since 90% of the businesses out there use Microsoft products, you would probably want to support their products in such a venture. On a personal note, I find anything that's not SQL Server to be tedious to work w…
honest question, when would you query several tables without joining them somewhere? you can do that on postgres as well btw, my first idea would be to create a function [1] and just throw the rows together. still can't imagine a usecase for that though, i'd always want to get them separately if they're unrelated. you're correct on the GUI side however. Everything i've tried is a buggy mess. i've actually mostly give…
I usually write queries that return multiple resultsets when the caller needs to get a bunch of data from different tables all at once. (So, all of the time.) Instead of sending each table query to the database server individually, I just query one "stored procedure" in SQL Server. It saves a lot of round-trips on the network and there are other benefits too.
For instance, say my API needs to take the relational data from tables `Customer, Order, OrderItem(s) and OrderShipment(s)` and return them as a JSON object `{order:{customer: {...}, items: [...], shipments: [...]}}`. Or, say I need to render a PDF using those same tables. In either case the client needs to get a whole graph of data out of the database and with SQL Server, they can only send one little query to call my procedure: `EXEC dbo.GetCustomerWithOrderDetail @OrderId=999;` instead of sending 4 or 5 single-resultset queries.
Sometimes I do also end up sending 2 or 3 procedure queries separately, for instance the client might send a query for `GetCustomerWithOrderDetail` followed by `GetPdfTemplateWithDetailForRendering`. So, I get code encapsulation too.
The benefits of this style are exponential in my opinion. Most of the time, my client apps don't even need to know the names of the tables because they are calling procedures, not embedding "SELECT * FROM TABLE" in the client code. The only minor drawback is that the client does need to know the order that the resultsets are returned in ahead of time.
I think there are a few ways to attempt this in PG but none of them seem to come very highly recommended. I looked around just now and found this post:
https://stackoverflow.com/questions/36717138/postgres-functi...
You can see the programmer wants to do what they need to in a very simple way, similar to how it would be done in SQL Server. However, it's not possible. Here is an article that explores some of the options in PG and the problems with each approach:
https://blog.dsl-platform.com/multiple-result-sets-alternati...