Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129
Given local join machinery, a join between 2 distributed tables can be achieved by joining the partitions of the tables locally after ensuring that tables are co-partitioned and partitions are co-located. That is, if performing A JOIN B ON (A.X = B.Y), ensure that A is partitioned by X and B is partitioned by Y with the same partitioning scheme such that every partition in A has a corresponding partition in B with which it can be joined. A distributed join can then be performed (in parallel) by joining each pair of partitions and concatenating the results.
Ideally, tables are co-partitioned and partitions are co-located in advance. The article describes scenarios in which the tables need to be repartitioned during the query, which can cause a lot of network traffic. However, the amount of traffic is limited in practice since filters and projections can be applied before shuffling any data.
The great thing about implementing a distributed database on top of PostgreSQL is that the local machinery already exists, and it is very good.
Generally if you have exposed your db credentials it's because something has gone very wrong i.e. your entire host has been compromised in which case stored procedures aren't going to save you.
And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.