| The databases I build exist only to serve the application and I prefer to tightly couple the data access to the database itself, thereby addressing the object/relational impedance mismatch in one simple stroke. 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... |