Postgres never gave me the ability to quickly and easily build stored procedures that return multiple heterogeneous resultsets with ease. In SQL Server you do it like this:
CREATE PROCEDURE get_customer_and_orders
@id int
BEGIN
SELECT id, first_name, last_name, email, etc FROM customers WHERE id = @id;
SELECT id, store_id, created_at, etc FROM customer_orders WHERE customer_id = @id;
END
I've quickly built entire applications with this tactic as the centerpiece. You can argue that it moves business logic into the database layer and to that I'd say "good", at least for apps that are maintained by IT departments with many strong SQL people and not so many developers. If you know TSQL, you know that you can also do branching, looping and other logic operations within this same procedure - you can even decide to send back 3 resultsets instead of 2 if you want to and the client API allows you to handle whatever it received quite elegantly.
I think that features like this are why many businesses will stay on SQL Server. Also the high quality of tools for SQL Server that have no match in Postgres such as SQL Server Management Studio, SQL Server Data Tools, SQL Server Profiler, SQL Server Integration Services among many other such tools that are extremely well integrated.
This isn't meant to detract from your point, but just a fun anecdote -- my first job out of college was working on a product that had its entire business logic layer built in stored procedures. All of it. Hundreds and hundreds of lines per procedure. It "worked", but man was maintenance a bear. In a way, it was good; ever since I've been very comfortable with SQL. But I can't recommend it.
My limited experience with smaller companies (and now local government) shares this sentiment. For “small” databases (say, not hundreds or more of terabytes) I’ve experienced using stored procedures as a core of the ETL process.
That being said, haven’t some recent versions of Postgres added support for stored procedures or some variant? I’m curious if we’d seen any changes in performance if we experimented with switching over.
The example you gave makes no sense. It would be better expressed as a join. There may be examples where it makes sense, but I can't think of any. In SQL Server, I think of a stored proc as a logical operation that takes action and returns one object. If I want sub-structure within that object, I have the stored proc return one json or xml object.
The example given allows the application layer to instantiate a customer object with a list of order objects in one database query. Yes, you could do that by returning JSON or XML, but why have the extra step of parsing the text? Directly accessing the result set and acting on the object instances will be more efficient.
No it's not. A join means you have to wastefully repeat the same parent fields for every row. That's wasted cycles serializing and deserializing and it's more work on the client.
It's absolutely ridiculous to make a blanket statement about this very useful feature.
I've done the benchmarking on many applications of this tactic. It very often works better with multiple select statements. I'll certainly trust hard performance numbers against an ill conceived opinion any day.
And yet, MARS is not great for troubleshooting or testing (for database people writing SQL) and I have worked at many places where the complexity of many sets doesn't really justify not making two procedures.
Perhaps I'm interpreting your code incorrectly, but this seems like you can do this with PG as well? You just define a custom type and use that type in the "returning" clause? You can easily define a bunch of scalars (customer_id, first_name, ...) and an array (customer_orders[]) inside this type.
That is one way to do it, kinda sucks compared to just returning multiple result sets because you have the overhead of maintaining those custom types for every type of query you're going to need. I don't personally like that solution much for complicated apps which will have a ton of queries written against their database.
I see that as an advantage. What you're really doing is defining the interface for that function, and once the function is in use, you can't just change it willy nilly. Having to alter the type is in some way a "are you sure you want to do that?" layer.
It would be extremely nice if you could just specify that directly in the function and not have that sprawl where it's unnecessary. For a single result set you do exactly that with PG, just wish it would allow you to specify multiple.
I think that features like this are why many businesses will stay on SQL Server. Also the high quality of tools for SQL Server that have no match in Postgres such as SQL Server Management Studio, SQL Server Data Tools, SQL Server Profiler, SQL Server Integration Services among many other such tools that are extremely well integrated.