> The problem is if you want things like custom group-bys, sorts, joins, windowing, etc, you end up reinventing a lot of concepts, or copy+pasting procedures with slight variations.
I could see how you could get that impression if you've worked on a project with poorly planned functions, but our experience has actually been the opposite of that. On the contrary, using PostgreSQL functions has allowed our company to be more consistent and declarative with our return data and has helped us eliminate repetitive SQL code. Particularly so when used in conjunction with our RESTful APIs.
Don't forget that in Postgres you can define custom composite return types (i.e., a return type that includes another return type). You can have a Postgres function that returns nested objects like this (I'm using JSON for clarity):
The ability to enforce that kind of return type for a given function is incredibly helpful
> It basically takes SQL's biggest issue, query composability, and makes it even worse than it already is.
I think that the bigger problems are ORMs like SQL Alchemy. Where it not only introduces a new layer of abstraction but also empowers engineers to request data from the database in a completely ad hoc "unregulated" manner. In large projects without a strong lead or code review process, it can quickly turn into a spaghetti mess that seriously complicates database re-factors.
When you use functions, you have clearly defined inputs and outputs, better re-usability and much more consistent interactions between the application layer and database layer.
I could see how you could get that impression if you've worked on a project with poorly planned functions, but our experience has actually been the opposite of that. On the contrary, using PostgreSQL functions has allowed our company to be more consistent and declarative with our return data and has helped us eliminate repetitive SQL code. Particularly so when used in conjunction with our RESTful APIs.
Don't forget that in Postgres you can define custom composite return types (i.e., a return type that includes another return type). You can have a Postgres function that returns nested objects like this (I'm using JSON for clarity):
[ "name":"Bob" ,"age":13 ,"address":"123 Main St" ,"cars":[ { "make":"Toyota" "model":"Camry" "license":"NF98549" }, { "make":"Toyota" "model":"Corolla" "license":"NF5649" }, ] ]
The ability to enforce that kind of return type for a given function is incredibly helpful
> It basically takes SQL's biggest issue, query composability, and makes it even worse than it already is.
I think that the bigger problems are ORMs like SQL Alchemy. Where it not only introduces a new layer of abstraction but also empowers engineers to request data from the database in a completely ad hoc "unregulated" manner. In large projects without a strong lead or code review process, it can quickly turn into a spaghetti mess that seriously complicates database re-factors.
When you use functions, you have clearly defined inputs and outputs, better re-usability and much more consistent interactions between the application layer and database layer.